I would like to create a macro which would resolve to in proc sql in the below pattern,
So I would like to create a macro which would dynamicaaly resolve to string of prior months value separated by commas.
Example for the month of MAY ..xyz macro resolve to
calculated Prior_Month1, calculated Prior_Month2, calculated Prior_Month3 ,calculated Prior_Month4
and the last month macro varibale resolve without comma.
I believe that you meant something like this:
Data Counter; Do i=1 To 4; * 4 Months; Output; End; Run; Proc SQL NoPrint; Select Cats("Calculated Prior_Month",Put(i,Best.)) Into :Months Separated By ', ' From Counter; Quit; %Put &Months.;
What's the rule, does it only go up to 4 because there are only 4 months prior to May? Is there more to your SQL, do you need to the single line generated only?
In my opinion, I generally prefer to transpose it to a long format and use no macros. The wide format is useful for reporting structures only.
This should get you started. Its not a macro but you could wrap it in a macro if you really wanted.
data query;
month=month(today());
length string $500.;
do i=1 to month-1;
string = cat(trim(string), " ", catt("Calculated Prior_Month", put(i, 2. -l), ","));
end;
call symputx("mvar", string);
run;
%put &mvar;
Thanks for your solution but there should not be a comma at the end of the string!.
Could you please give an easy solution to this,?
Thanks
I believe that you meant something like this:
Data Counter; Do i=1 To 4; * 4 Months; Output; End; Run; Proc SQL NoPrint; Select Cats("Calculated Prior_Month",Put(i,Best.)) Into :Months Separated By ', ' From Counter; Quit; %Put &Months.;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.