Solved

What code to set up for repetitive task?

Posted on 2011-02-24
5
470 Views
Last Modified: 2013-11-16
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
Comment
Question by:appc
  • 3
  • 2
5 Comments
 
LVL 11

Assisted Solution

by:theartfuldazzler
theartfuldazzler earned 100 total points
ID: 34977407
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
 

Author Comment

by:appc
ID: 34979984
There is one thing that you should know: there are thousands of IDs in table A...
0
 
LVL 11

Assisted Solution

by:theartfuldazzler
theartfuldazzler earned 100 total points
ID: 34994681
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
 

Accepted Solution

by:
appc earned 0 total points
ID: 35477709
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
 

Author Closing Comment

by:appc
ID: 35503382
I figured it out by myself.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now