topic Re: Macro String in Sql in SAS Programming

Macro String in Sql

sdixit — Fri, 17 Jun 2016 00:58:58 GMT

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.

Re: Macro String in Sql

Reeza — Fri, 17 Jun 2016 01:20:00 GMT

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.

Re: Macro String in Sql

Reeza — Fri, 17 Jun 2016 01:58:27 GMT

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;

Re: Macro String in Sql

sdixit — Fri, 17 Jun 2016 03:01:18 GMT

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

Re: Macro String in Sql

user24feb — Fri, 17 Jun 2016 05:31:37 GMT

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