Solved

What code to set up for repetitive task?

Posted on 2011-02-24
5
476 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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