Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

What method can be used to run a segment of code iteratively with different date values?

I have the following segment code below that I have to change the dates to get a full dataset for the range of dates. For this one the dates will be from October 2009 to August 2010.

rsubmit;
%macro t1(table,step,start,end);
proc sql;
create table &table as
select *
from Month_Jun10
where &step between &start and &end;
quit;
%mend;
%t1(step22,step22_date,'01JUL2010'D,'31JUL2010'D);
%t1(step10,step10_date,'01JUL2010'D,'31JUL2010'D);
%t1(step16,step16_date,'01JUL2010'D,'31JUL2010'D);
%t1(step16,step16_date,'01JUL2010'D,'31JUL2010'D);
proc sql;
create table REM as
select *
from Month_Jun10
where Add_date between 20100701 and 20100731
and active_flg eq 'NotActiveLM'
and step22_date eq .
and step10_date eq .
and step16_date eq .
and step16_date eq .;
quit;
data Total;
set step22 step10 step16 step16 REM;run;
endrsubmit;

The creation of the "month" tables requires the same changes.

rsubmit;
%macro mth1(pd,start,end);
proc sql;
create table Month_&pd as
select *
from dima.hamp_daily_&gdate
where step45_date between &start and &end;
quit;
%mend;
%mth1(OCT09,'01OCT2009'D,'31OCT2009'D);
%mth1(NOV09,'01NOV2009'D,'30NOV2009'D);
%mth1(DEC09,'01DEC2009'D,'31DEC2009'D);
%mth1(JAN10,'01JAN2010'D,'31JAN2010'D);
%mth1(FEB10,'01FEB2010'D,'28FEB2010'D);
%mth1(MAR10,'01MAR2010'D,'31MAR2010'D);
%mth1(APR10,'01APR2010'D,'30APR2010'D);
%mth1(MAY10,'01MAY2010'D,'31MAY2010'D);
%mth1(JUN10,'01JUN2010'D,'30JUN2010'D);
%mth1(JUL10,'01JUL2010'D,'30JUL2010'D);
endrsubmit;

Thanks in advance,
SASnewbie
0
SASnewbie
Asked:
SASnewbie
  • 6
1 Solution
 
SASnewbieAuthor Commented:
I need to add more explanation to what changes.
for each month; oct09 through aug10, the start and end dates have to run through 01OCT2009 through 31AUG2010,
So Month_Oct09 has to run it's segment 11 times for the following start and end dates:

'01OCT2009'D,'31OCT2009'
'01NOV2009'D,'30NOV2009'
'01DEC2009'D,'31DEC2009'
'01JAN2010'D,'31JAN2010'
'01FEB2010'D,'28FEB2010'
'01MAR2010'D,'31MAR2010'
'01APR2010'D,'30APR2010'
'01MAY2010'D,'31MAY2010'
'01JUN2010'D,'30JUN2010'
'01JUL2010'D,'30JUL2010'
'01AUG2010'D,'31AUG2010'
 Then the same process is repeated for Month_Nov09, through Month_Aug10.
Thanks again,
SASnewbie
0
 
SASnewbieAuthor Commented:
Attached is where the captured output looks like:
     

Doc3.doc
0
 
SASnewbieAuthor Commented:
The "Total" value for each iteration is the incremental value for that "Month_XXX".
Thanks again,
 
0
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!

 
SASnewbieAuthor Commented:
Please change this question to the Database -> SAS zone.
Thanks,
0
 
theartfuldazzlerCommented:
Hi SASNewbie

I do a lot of repeated code "per month", and make use of the INTNX function. (see SAS help for details.

The general code below works well - you look like you understand macros, so you would need to fill in your steps that are particular to you.  Hope this helps:



%MACRO XXX (date);

%Let mon = %SYSFUNC(PUTN(&date,MONYY5.));  /*  Create variable to look like MMMYY */
%Put mon = &mon;

%Let Start = %SYSFUNC(INTNX(MONTH,&date,0,b));     /* Create Date at start of month */
%PUT Start = &Start (%SYSFUNC(PUTN(&start,date9.)) );

%Let End = %SYSFUNC(INTNX(MONTH,&date,0,e));   /* Create date at end of month */
%PUT End = &End (%SYSFUNC(PUTN(&end,date9.)) );
%PUT ******************** ;

%MEND;

%Macro Repx;

%Let Datex = %SYSEVALF("31Jan2009"d);  /* hardcode the date */

%let Datex = %SYSFUNC(INTNX(Month, %SYSFUNC(TODAY()), -12, b));  /* Start from 12 months before today */

%DO %UNTIL (&datex >= %SYSFUNC(TODAY()) );
   %xxx(&datex);
   %let datex = %SYSFUNC(INTNX(MONTH,&datex,1,e)); /* Increment the datex variable by 1 month */
%END;
%MEND;

%Repx;
0
 
SASnewbieAuthor Commented:
Hi theartfuldazzler,
Thank you for responding.
Your code looks really good. I will work on it shortly and get back to you.
Thanks!
SASnewbie
0
 
SASnewbieAuthor Commented:
Hi again theartfuldazzler,
This is the output from your code and is exactly what I am looking for:
mon = SEP09
Start = 18141 (01SEP2009 )
End = 18170 (30SEP2009 )
********************
mon = OCT09
Start = 18171 (01OCT2009 )
End = 18201 (31OCT2009 )
********************
mon = NOV09
Start = 18202 (01NOV2009 )
End = 18231 (30NOV2009 )
********************
mon = DEC09
Start = 18232 (01DEC2009 )
End = 18262 (31DEC2009 )
********************
mon = JAN10
Start = 18263 (01JAN2010 )
End = 18293 (31JAN2010 )
********************
mon = FEB10
Start = 18294 (01FEB2010 )
End = 18321 (28FEB2010 )
********************
mon = MAR10
Start = 18322 (01MAR2010 )
End = 18352 (31MAR2010 )
********************
mon = APR10
Start = 18353 (01APR2010 )
End = 18382 (30APR2010 )
********************
mon = MAY10
Start = 18383 (01MAY2010 )
End = 18413 (31MAY2010 )
********************
mon = JUN10
Start = 18414 (01JUN2010 )
End = 18443 (30JUN2010 )
********************
Fantastic!!!!
Thanks,
 SASnewbie
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.

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