DATA Step, Macro, Functions and more

PROC SQL, into Macro variable assignment problems

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

PROC SQL, into Macro variable assignment problems

Hello,

 

I am having trouble assigning macro variables that work within the proc sql into function. When I run the code below, the assignment of the macro variable sort of "cuts off" at year, such that the macro variable follows the assignment shown in the picture below.

 

Although when I use the basic %let way of assignment, it works fine.

 

PROC sql;
		SELECT DISTINCT 
			CASE 
				WHEN month("&ReportDate"d)=12 THEN "('CY 1st Half','CY 2nd Half')" 
				WHEN month("&ReportDate"d)=6 THEN "('CY 1st Half')" 
			END 
		AS period, 
		CASE 
				WHEN month("&ReportDate"d)=12 THEN "YE" 
				WHEN month("&ReportDate"d)=6 THEN "MY" 
			END 
		AS period2, 
		strip(calculated period2) as period3,
strip(substr(compress(tranwrd(put(intnx('qtr',"&ReportDate"d,-1,'E'),yymmdd10.),"-","")),1,4)) as year
into : Period, : Period2, : Period3,  : Year
FROM  _PRODSAVAIL;
quit;
%let libname = \\BPR\BPR 2.0 (New BPR)\Data\&year.\&period3;

Capture.JPG

Accepted Solutions
Solution
‎06-26-2017 02:34 PM
Super User
Super User
Posts: 6,499

Re: PROC SQL, into Macro variable assignment problems

Hard to tell without sample data so that we know how the input data is formatted, but the most obvious thing to watch out for is extra spaces in the generated macro variables.

You can add the TRIMMED keyword into the INTO clause to have it automatically trim the values.

Check out this example:

proc sql noprint;
  select age,age
    into :notrim , :trimmed trimmed
    from sashelp.class (obs=1)
  ;
quit;
%put notrim=|&notrim| trimmed=|&trimmed|;
notrim=|      14| trimmed=|14|

View solution in original post


All Replies
Contributor
Posts: 27

Re: PROC SQL, into Macro variable assignment problems

Note that I've managed to solve the problem using the following re-assingment:

 

%let year= &year;

%let period = &period3;

 

but is there a way to go around that step?

 

Solution
‎06-26-2017 02:34 PM
Super User
Super User
Posts: 6,499

Re: PROC SQL, into Macro variable assignment problems

Hard to tell without sample data so that we know how the input data is formatted, but the most obvious thing to watch out for is extra spaces in the generated macro variables.

You can add the TRIMMED keyword into the INTO clause to have it automatically trim the values.

Check out this example:

proc sql noprint;
  select age,age
    into :notrim , :trimmed trimmed
    from sashelp.class (obs=1)
  ;
quit;
%put notrim=|&notrim| trimmed=|&trimmed|;
notrim=|      14| trimmed=|14|
Contributor
Posts: 27

Re: PROC SQL, into Macro variable assignment problems

I tried that and it still causes the same problem. So far the only thing that I found works is reassigning again using %let = &year (weird.)
Super User
Posts: 17,784

Re: PROC SQL, into Macro variable assignment problems

@camfarrell25 The TRIMMED option doesn't work for you?

Contributor
Posts: 27

Re: PROC SQL, into Macro variable assignment problems

ah my mistake!! I'm fairly new at this. I didn't know how to properly use the trimmed option...
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 124 views
  • 0 likes
  • 3 in conversation