PROC SQL, into Macro variable assignment problems

[camfarrell25]

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;

[camfarrell25]

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?

[Tom · Accepted solution]

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|

[camfarrell25]

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

[Reeza]

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

[camfarrell25]

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