Solved

What code to set up for repetitive task?

Posted on 2011-02-24
5
467 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CDC audit 17 84
What is ISQL? 6 99
Insert with SET how to handle join 6 27
Pivot not using aggregate yield error 3 15
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 …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

708 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

16 Experts available now in Live!

Get 1:1 Help Now