DATA Step, Macro, Functions and more

Macro String in Sql

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Macro String in Sql

[ Edited ]

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.

 

 

 


Accepted Solutions
Solution
‎06-17-2016 03:06 PM
Super Contributor
Posts: 336

Re: Macro String in Sql

I believe  Smiley Wink 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.;

View solution in original post


All Replies
Super User
Posts: 17,837

Re: Macro String in Sql

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.

Super User
Posts: 17,837

Re: Macro String in Sql

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;
Contributor
Posts: 26

Re: Macro String in Sql

[ Edited ]

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

 

Solution
‎06-17-2016 03:06 PM
Super Contributor
Posts: 336

Re: Macro String in Sql

I believe  Smiley Wink 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.;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 262 views
  • 0 likes
  • 3 in conversation