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.
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
%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
Have a look at the sample below
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
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 ;
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 ???
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.