BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
forumsguy
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Vince28_Statcan
Quartz | Level 8

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

6 REPLIES 6
UrvishShah
Fluorite | Level 6

%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

BrunoMueller
SAS Super FREQ

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
Vince28_Statcan
Quartz | Level 8

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

bwasicak
Calcite | Level 5

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 ;

forumsguy
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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