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
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;
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.
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;
Thanks Tom and Astounding,
It works! Thanks a lot for your help guys.
I used the code Tom provided me.
Regards,
Rishi
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: