[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

What code to set up for repetitive task?

Posted on 2011-02-24
5
Medium Priority
?
478 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 400 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 400 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

656 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