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.;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.