## Macro loop for months

Solved
Frequent Contributor
Posts: 85

# 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

 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.

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

## Re: Macro loop for months

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

All Replies
Regular Contributor
Posts: 195

## Re: Macro loop for months

%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: 825

## Re: Macro loop for months

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

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

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

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
Posts: 8,115

## Re: Macro loop for months

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.