topic Re: Macro loop for months in SAS Programming

Macro loop for months

forumsguy — Thu, 29 Aug 2013 11:53:09 GMT

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

var1	var2	flag_1
a	d	y
b	e	y
c	f	n

have_201202

var1	var2	flag_2
d	a	n
e	b	y
f	c	n

have_201203

var1	var2	flag_3
p	a	n
q	b	y
r	c	n

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.

Re: Macro loop for months

UrvishShah — Thu, 29 Aug 2013 12:21:07 GMT

%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

Re: Macro loop for months

BrunoMueller — Thu, 29 Aug 2013 12:29:50 GMT

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

Re: Macro loop for months

Vince28_Statcan — Thu, 29 Aug 2013 12:59:34 GMT

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

Re: Macro loop for months

bwasicak — Thu, 29 Aug 2013 14:29:40 GMT

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 ;

Re: Macro loop for months

forumsguy — Sat, 07 Sep 2013 16:57:42 GMT

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 ???

Re: Macro loop for months

Tom — Sat, 07 Sep 2013 18:23:48 GMT

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