Macro String in Sql

[sdixit]

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.

[Reeza]

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.

[Reeza]

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;

[sdixit]

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

[user24feb · Accepted solution]

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.;