topic Re: PROC SQL, into Macro variable assignment problems in SAS Programming

PROC SQL, into Macro variable assignment problems

camfarrell25 — Mon, 26 Jun 2017 18:11:12 GMT

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;

Re: PROC SQL, into Macro variable assignment problems

camfarrell25 — Mon, 26 Jun 2017 18:16:42 GMT

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?

Re: PROC SQL, into Macro variable assignment problems

Tom — Mon, 26 Jun 2017 18:18:09 GMT

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|

Re: PROC SQL, into Macro variable assignment problems

camfarrell25 — Mon, 26 Jun 2017 18:24:40 GMT

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

Re: PROC SQL, into Macro variable assignment problems

Reeza — Mon, 26 Jun 2017 18:27:57 GMT

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

Re: PROC SQL, into Macro variable assignment problems

camfarrell25 — Mon, 26 Jun 2017 18:35:07 GMT

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