DATA Step, Macro, Functions and more

Macro loop for months

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 85
Accepted Solution

Macro loop for months

Hi All,

I have 12 datasets starting with names as have_201201,have_201202,have_201203,have_201204...have_201212. These have 3 variables named var1,var2 and flag.. Flag variable's name is flag_1 for dataset 1, flag_2 for dataset2 and so on and has values "y" or "n" . i need to create subsets of this data for each month for flag value "Y" using sql.  And this is only for one year, I need this macro to be generalised to be used for any year.

I am stuck only with the month values , how the loop should go from 201201 to 201212...

my datasets look like this

have_201201

var1var2flag_1
ady
bey
cfn

have_201202

var1var2flag_2
dan
eby
fcn

have_201203

var1var2flag_3
pan
qby
rcn

Now, the sql which I am using is as below:

Proc sql;

create table want as

select var1,var2,flag_1 from have_201201 where flag_1='y'

quit;

I am stuck in putting this code in macro as otherwise I would need to run this code for each month and need to change flag values as well...

Any help is really appreciated.


Accepted Solutions
Solution
‎08-29-2013 08:59 AM
Super Contributor
Posts: 339

Re: Macro loop for months

Posted in reply to forumsguy

You can use either SASHELP.Vcolumn and/or SASHELP.Vtable to help you retrieve the tables full name/variable names. However, if you always have exactly 12 datasets, you could do a fairly simple macro to loop on all 12 datasets. Regardless of the approach, some macroing will be necessary.

If you have always exactly 12 months, you could do half hard code half macroing like

%macro flagged(year=)

data want;

     set have_&year.01(where=(flag_1='Y') rename=(flag_1=flag))

          have_&year.02(where=(flag_2='Y') rename=(flag_2=flag))

          ...

          have_&year.12(where=(flag_12='Y') rename=(flag_12=flag))

          ;

     /* other processing if desired */

run;

%mend;

%flagged(year=2012);

This is somewhat tedious as you have to write the 01/1 everywhere. What can be done instead is use some more macroing

%macro flagged(year=)

data want;

     set %do i=1 %to 12;

          have_&year.%sysfunc(putn(&i., z2.)(where=(flag_&i.='Y') rename=(flag_&i.=flag))

          %end;

          ; /*the semi column ending the set statement */

     /* other processing if desired */

run;

%mend;

%flagged(year=2012);

If there is a chance that you will run on an incomplete year, then there is a way to achieve the desired results with vtable/vcolumn

%macro flagged(year= , libname=);

     proc sql;

          select memname

          into :dsname1-:dsname12

          from sashelp.vtable

          where libname=%upcase(&libname.)

                    and

                     substr(libname, 1, 9)="have_&year."

          ;

     quit;

data want;

     set %do i=1 %to 12

          &libname..have_&year.%sysfunc(putn(&i., z2.).(where=(flag_&i.='Y') rename=(flag_&i.=flag))

          %end;

          ; /*the semi column ending the set statement */

     /* other processing if desired */

run;

%mend;

%flagged(year=2012, libname=work);

*edited immediately after seeing previous post noting that %sysfunc supports putn!

The renames are just so that you don't have flag_1 to flag_12 in your resulting dataset with a bunch of missing values

Vincent

View solution in original post


All Replies
Regular Contributor
Posts: 195

Re: Macro loop for months

Posted in reply to forumsguy

%macro test(year);

 

      %let list = 01 02 03 04 05 06 07 08 09 10 11 12;   

      %let i = 1;

      %let _list = %scan(&list.,&i.);

      %do %while(&_list ^=);

         proc sql;

           create table want_&year.&_list. as

           select *

           from have_&year.&_list.(where = (upcase(flag_&i.) = "Y"));

         quit;

                 %let i = %eval(&i.+1);

                 %let _list = %scan(&list.,&i.);

      %end;

%mend;

/* Sample Call */

%test(2012);

%test(2013);

-Urvish

SAS Super FREQ
Posts: 708

Re: Macro loop for months

Posted in reply to forumsguy

Have a look at the sample below

data have_201201;
  infile cards ;
  input
    var1 $
    var2 $
    flag_1 $
  ;
cards;
a d y
b e y
c f n
;

data have_201202;
  infile cards ;
  input
    var1 $
    var2 $
    flag_2 $
  ;
cards;
d a n
e b y
f c n
;


data have_201203;
  infile cards ;
  input
    var1 $
    var2 $
    flag_3 $
  ;
cards;
p a n
q b y
r c n
;

%macro myloop;
  proc sql;
    drop table want;
  quit;
 
%local tempTable;
  %do year = 2012 %to 2012;
   
%do month = 1 %to 3;
     
%let tempTable = have_&year%sysfunc(putn(&month, z2.));
      Proc sql feedback;
        create table want_temp as
          select
            var1, var2, flag_&month as flag
          from
            &tempTable
          where
            flag_&month =
'y'
        ;
      quit;
      proc append
        base=want
        data=want_temp
      ;
      run;
   
%end;
 
%end;
%mend;

%
myLoop
Solution
‎08-29-2013 08:59 AM
Super Contributor
Posts: 339

Re: Macro loop for months

Posted in reply to forumsguy

You can use either SASHELP.Vcolumn and/or SASHELP.Vtable to help you retrieve the tables full name/variable names. However, if you always have exactly 12 datasets, you could do a fairly simple macro to loop on all 12 datasets. Regardless of the approach, some macroing will be necessary.

If you have always exactly 12 months, you could do half hard code half macroing like

%macro flagged(year=)

data want;

     set have_&year.01(where=(flag_1='Y') rename=(flag_1=flag))

          have_&year.02(where=(flag_2='Y') rename=(flag_2=flag))

          ...

          have_&year.12(where=(flag_12='Y') rename=(flag_12=flag))

          ;

     /* other processing if desired */

run;

%mend;

%flagged(year=2012);

This is somewhat tedious as you have to write the 01/1 everywhere. What can be done instead is use some more macroing

%macro flagged(year=)

data want;

     set %do i=1 %to 12;

          have_&year.%sysfunc(putn(&i., z2.)(where=(flag_&i.='Y') rename=(flag_&i.=flag))

          %end;

          ; /*the semi column ending the set statement */

     /* other processing if desired */

run;

%mend;

%flagged(year=2012);

If there is a chance that you will run on an incomplete year, then there is a way to achieve the desired results with vtable/vcolumn

%macro flagged(year= , libname=);

     proc sql;

          select memname

          into :dsname1-:dsname12

          from sashelp.vtable

          where libname=%upcase(&libname.)

                    and

                     substr(libname, 1, 9)="have_&year."

          ;

     quit;

data want;

     set %do i=1 %to 12

          &libname..have_&year.%sysfunc(putn(&i., z2.).(where=(flag_&i.='Y') rename=(flag_&i.=flag))

          %end;

          ; /*the semi column ending the set statement */

     /* other processing if desired */

run;

%mend;

%flagged(year=2012, libname=work);

*edited immediately after seeing previous post noting that %sysfunc supports putn!

The renames are just so that you don't have flag_1 to flag_12 in your resulting dataset with a bunch of missing values

Vincent

Occasional Contributor
Posts: 14

Re: Macro loop for months

Posted in reply to Vince28_Statcan

How about a simple macro with a do loop?

%macro countem;

%let flg=1;

  %do  I = 201201 %to  201203;

     %if &I >  201201 %then %do;

        %let flg=%eval(&flg+1);

   %end;  

    Proc sql;

         create table want as

         select var1

                   ,var2,

                   flag_&flg

         from have_&I

        where flag_1='y'

      quit;

     proc datasets;

             append base=wantall   data=want force;

            delete want;

   quit;  

  %end;

%mend countem;

%countem ;

Frequent Contributor
Posts: 85

Re: Macro loop for months

Posted in reply to Vince28_Statcan

Thanks Vincent. Also is there way we could use intnx function here ? As per my understanding we can use it for something like jan13 feb 13 and then format it ... am I going in correct direction here ???

Super User
Super User
Posts: 7,042

Re: Macro loop for months

Posted in reply to forumsguy

If you want to loop over months then INTNX is useful.

%let start=JUN2012 ;

%let nmonths=12;

%do i=1 %to &nmonths;

    %let month=%sysfunc(intnx(month,"01&start"d,&i-1),yymmn6.);

    %put i=&i month=&month;

%end;


i=1 month=201206

i=2 month=201207

i=3 month=201208

i=4 month=201209

i=5 month=201210

i=6 month=201211

i=7 month=201212

i=8 month=201301

i=9 month=201302

i=10 month=201303

i=11 month=201304

i=12 month=201305

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1156 views
  • 0 likes
  • 6 in conversation