[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

What code to set up for repetitive task?

Hi,

I have the following table (let's call it A):

ID         Date    
 1      02FEB2010  
 4      31JAN2010
....

I have other historical tables:
Output_Feb2010
Output_Mar2010
....
Output_Jan2011

The general structure of each table is the following:
id      date             amount
1      ddmmyyyy       100
2      ddmmyyyy          -

What I want to do is, for each id from the first table , add the amount field from the historical tables for each month after the month from A and call the new field 'Month 1', 'Month 2' etc....

The final table would be something like that:

ID         Date            Month1                                                           Month2                         ...............          Month10
 1      02FEB2010      amount from march historical table      amount from april historical table ...............
 4      31JAN2010      amount from feb historical table           amount from march historical table  ..................

This is pretty hard....

Thanks

A
0
appc
Asked:
appc
  • 3
  • 2
3 Solutions
 
theartfuldazzlerCommented:
Hi

See if you can make sense of the following code:


It does assume that all the relevant tables exist...
/** Set up testing databases **/;
DATA Table_A;
  ID = 1;
  Date = '02Feb2010'd;
  OUTPUT;
  ID = 4;
  Date = '31Jan2010'd;
  OUTPUT;
format date date9.;
RUN;

DATA Output_Feb2010
     Output_Mar2010
     Output_Apr2010
     Output_May2010
     Output_Jun2010
     Output_Jul2010
     Output_Aug2010
     Output_Sep2010
     Output_Oct2010
     Output_Nov2010
     Output_Dec2010;

  ID = 1;
  Date = '02Feb2010'd;
  Amount = 100 ;
  OUTPUT;
  ID = 4;
  Date = '31Jan2010'd;
  Amount = 200 ;
  OUTPUT;
  FORMAT Date date9.;
RUN;

/* end creating testing databases */


proc sql;
  create table final (ID num, date num format=date9.,
	month1 num,
	month2 num,
	month3 num,
	month4 num,
	month5 num,
	month6 num,
	month7 num,
	month8 num,
	month9 num,
	month10 num);


%MACRO Get_record(id, date);

DATA Temp;
ID = &id;
Date = &date;
format date date9.;
RUN;
PROC SQL;
%let datex = %SYSFUNC(INTNX(month,&date,1));
%DO %UNTIL(&datex >= %SYSFUNC(INTNX(Month,&date,10,e)) );
 %let month_diff = %SYSFUNC(INTCK(MONTH,&date,&datex));
   
   create table temp as
	  select a.*, b.Amount as Month&Month_diff
	    from temp as a
		left join output_%SYSFUNC(PUTN(&datex,monyy7.)) as b
		on a.id = b.id and a.date = b.date;

 %let datex = %SYSFUNC(INTNX(month,&datex,1));
%END;

  insert into final
    select * from temp;

  drop table temp;

%MEND;


DATA _NULL_;
  set Table_A;

  CALL EXECUTE('%Get_Record('||id||','||date||')' );  /* get the entire line and insert into table "final" */

RUN;

Open in new window

0
 
appcAuthor Commented:
There is one thing that you should know: there are thousands of IDs in table A...
0
 
theartfuldazzlerCommented:
Hi

I had some time to think about this this weekend.

I have re-written the code, so that it only works thru each "Output" summary file once.  Let me know if you have any questions:

/** Set up testing databases **/;
DATA Table_A;
  ID = 1;
  Date = '02Feb2010'd;
  OUTPUT;
  ID = 4;
  Date = '31Jan2010'd;
  OUTPUT;
format date date9.;
RUN;

DATA Output_Feb2010
     Output_Mar2010
     Output_Apr2010
     Output_May2010
     Output_Jun2010
     Output_Jul2010
     Output_Aug2010
     Output_Sep2010
     Output_Oct2010
     Output_Nov2010
     Output_Dec2010;

  ID = 1;
  Date = '02Feb2010'd;
  Amount = 100 ;
  OUTPUT;
  ID = 4;
  Date = '31Jan2010'd;
  Amount = 200 ;
  OUTPUT;
  FORMAT Date date9.;
RUN;

/* end creating testing databases */

proc sql stimer;
create table final as
  select *, . as Month1,
	 . as Month2,
	  . as Month3,
	   . as Month4,
	    . as Month5,
		 . as Month6,
		  . as Month7,
		   . as Month8,
		    . as Month9,
			 . as Month10
from Table_A
order by id, date;

/* 
** Alternate method to create table "Final";
DATA Final;
  set Table_A;
  ARRAY Month(10) Month1-Month10;
RUN;
PROC SORT DATA=Final;
BY ID Date;
RUN;

*/

%MACRO TTT(date);
  %let date = %SYSEVALF(&date);
  %let monyy = %SYSFUNC(PUTN(&date,monyy7.));
  %IF %SYSFUNC(EXIST(Output_&monyy)) %THEN %DO;
	  Proc sql stimer;
	    create view Output_&monyy._v as
		  select *, INTCK("MONTH", date, &date) as Month_diff from Output_&monyy
		  order by id, date;

		DATA Final;
			MERGE Final Output_&monyy._v;
			BY ID Date;

			ARRAY MONTH(10) Month1-Month10;

			IF 1 <= Month_diff <= 10 THEN Month(Month_diff) = Amount;

			DROP Amount Month_diff;
    RUN;
  %END;
%MEND;

/*
%TTT('01Mar2010'd);

*/


%MACRO Repeat;

%let date = %SYSEVALF("01Feb2010"d);

%DO %UNTIL (&date > %SYSFUNC(TODAY()));
  %TTT(&date);
  %let date = %SYSFUNC(INTNX(Month,&date,1,e));
%END;
%MEND;

%Repeat;

Open in new window

0
 
appcAuthor Commented:
data tdr_200812;
set tdr;
run;

%macro datap(year=,prev=);

proc sql;
create table TDR_&year as
select a.*,
            b.PD_model as PD_&year

from TDR_&prev      a
left join      mortgage.Compbasel_&year b on a.AR_ID=b.AR_ID
;
quit;


%mend;

%datap(year=200901, prev=200812);
%datap(year=200902, prev=200901);
%datap(year=200903, prev=200902);
%datap(year=200904, prev=200903);
%datap(year=200905, prev=200904);
%datap(year=200906, prev=200905);
%datap(year=200907, prev=200906);
%datap(year=200908, prev=200907);
%datap(year=200909, prev=200908);
%datap(year=200910, prev=200909);
%datap(year=200911, prev=200910);
%datap(year=200912, prev=200911);
%datap(year=201001, prev=200912);
%datap(year=201002, prev=201001);
%datap(year=201003, prev=201002);
%datap(year=201004, prev=201003);
%datap(year=201005, prev=201004);
%datap(year=201006, prev=201005);
%datap(year=201007, prev=201006);
%datap(year=201008, prev=201007);
%datap(year=201009, prev=201008);
%datap(year=201010, prev=201009);
%datap(year=201011, prev=201010);
%datap(year=201012, prev=201011);
%datap(year=201101, prev=201012);
%datap(year=201102, prev=201101);



data TDRtoTranspose1;
set tdr_201102;
if exclude not in ('ex');
if ar_id not in ('');
run;

data TDRtoTranspose;
set TDRtoTranspose1;
keep AR_ID decisiondate PD_200901 PD_200902 PD_200903 PD_200904 PD_200905 PD_200906 PD_200907 PD_200908 PD_200909 PD_200910 PD_200911 PD_200912 PD_201001 PD_201002 PD_201003 PD_201004 PD_201005 PD_201006 PD_201007 PD_201008 PD_201009 PD_201010 PD_201011 PD_201012 PD_201101 PD_201102;
run;

proc sort data=TDRtoTranspose;
by AR_ID decisiondate;
run;

proc transpose data=TDRtoTranspose out=tdrtrans;
by AR_ID decisiondate;
run;

data transposed;
format date date9.;
set tdrtrans;
Month=substr(_Name_,8,2);
Year=substr(_name_,4,4);

Date=mdy(Month,1,Year);
difference=intck('month',decisiondate,date);
run;

proc sort data=transposed nodup;
by ar_id decisiondate difference;
run;

proc transpose data=transposed out=final;
by AR_ID decisiondate;
var COL1;
id difference;
run;
0
 
appcAuthor Commented:
I figured it out by myself.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now