Question on iterative macro processing

[RishiThakur]

Dear all,

After transposing a column on weeks I would like to have it sorted properly. The columns are named wk201301, wk201302 till the current week.

I have written a code which adds the weeknumbers so I can use it in a retain step to sort the columns of week like this:

data test2;

     retain wk201301, wk201302, wk201303, wk201304;

     set test;

run;

It is too intensive to have this code manually so I have made a macro which does this for me:

proc sql noprint;
   select count(*)
      into :NObs
           from test;
quit;

proc sql noprint;
   select vname
      into :week1-:week&NObs
           from test;
quit;

except the macro is not working as it doesn't count the macro till the week it should do in this case week 21. So that I can use it in a datastep to sort the columns like:

data test;

retain &week1-&weekx;

set datasetx;

run;

How can I fix this so that the macro creates week1 till week21? Any help would be greatly appreciated.

Regards,

Rishi

[Astounding]

The first problem you are hitting is that &NOBS has the wrong value in it.  It contains leading blanks.  So you would be getting an error trying to code the equivalent of:

into : week1 - week      21

There are two easy ways to fix this.  Pick one ...

Insert between the two PROC SQLs:  %let nobs = &nobs;

Change the second PROC SQL:

into : week1 - week&sqlobs

Sometimes taking care of the first problem fixes the rest, sometimes not.  If that doesn't take care of it, it would be helpful to see the results of %PUT &NOBS;

Good luck.

[Tom · Accepted solution]

Syntax on RETAIN statement does not use commas.

No you know where to get the variable names?  In your example you are pulling from a variable named VNAME in a table named TEST, but then you are using that same table as the one that has actual data with the WKxxxxxx variables.

Once you have the variable names as data then you can use the SEPARATED BY clause on INTO store all of the names into one macro variable. (Note there are limits to the length of a macro variable).

proc sql noprint;

  select vname into :varlist separated by ' '

    from test

  ;

quit;

data want ;

  retain &varlist;

  set have;

run;

If you want to query the variables in the source dataset then you can use DICTIONARY.COLUMN view.

proc sql noprint;

  select name into :varlist separated by ' '

    from dictionary.column

    where libname='WORK' and memname='HAVE'

      and upcase(name) like 'WK%'

    order by upcase(name)

  ;

quit;

[RishiThakur]

Thanks Tom and Astounding,

It works! Thanks a lot for your help guys.

I used the code Tom provided me.

Regards,

Rishi