Macro variables: Why is there whitespace between 2 macro variables

Jesper van Beemdelust 0
Viewed 47

Why does this return spaces/tab between 2 variables(JAN 2006)?

I have pasted my code below.

I am trying to automate the date in the filename. The problem here is is that it's used multiple times in the query and it's usually run backwards in time on a monthly basis.

So I have setup a simple macro variable that filters on the dates, but the filename isn't as easily updated. Any tips or suggestions?

If it wasn't on a monthly basis I would have simply used %sysdate function.

Example filename

PROC SQL;
    CREATE TABLE TEST AS    
        SELECT * FROM SASHELP.RENT
        ;
RUN;

PROC SQL;
        SELECT TRIM(YEAR(MAX(date)))
        INTO :year
        FROM test;
RUn;

PROC SQL;
        SELECT 
            CASE 
                WHEN MONTH(MAX(DATE))=1
                THEN "JAN"
                WHEN MONTH(MAX(DATE))=2
                THEN "FEB"              
                WHEN MONTH(MAX(DATE))=3
                THEN "MAR"          
                WHEN MONTH(MAX(DATE))=4
                THEN "APR"              
                WHEN MONTH(MAX(DATE))=5
                THEN "MEI"              
                WHEN MONTH(MAX(DATE))=6
                THEN "JUN"              
                WHEN MONTH(MAX(DATE))=7
                THEN "JUL"              
                WHEN MONTH(MAX(DATE))=8
                THEN "AUG"              
                WHEN MONTH(MAX(DATE))=9
                THEN "SEP"              
                WHEN MONTH(MAX(DATE))=10
                THEN "OKT"              
                WHEN MONTH(MAX(DATE))=11
                THEN "NOV"
                WHEN MONTH(MAX(DATE))=12
                THEN "DEC"
                END AS maand
        INTO :maand
        FROM test;
RUn;


PROC EXPORT 
DATA=test
outfile = "\\test-&maand&year"
dbms =xlsx replace;
RUN;
sas-macro sas
2 Answers

Here is how I would simplify it.

proc sql noprint;
  select year(date)
       , put(month(date), monname3.) 
  into :year separated by ''
     , :month separated by ''
  from sashelp.rent
  having max(date) = date;
run;

%put &year.;
%put &month.;

%put \\test-&month.&year.;
PeterClemmensen0

You cannot use the character function TRIM() on a numeric value.

11   select trim(year(max(date))) into :year from sashelp.rent;
ERROR: Function TRIM requires a character expression as argument 1.

If you don't want spaces in the value of the macro variable YEAR then do not add the spaces to begin with.

For example you could use one of these methods:

select year(max(date)) into :year trimmed from sashelp.rent;
select max(date) format=year4. into :year from sashelp.rent;
select year(max(date)) format=4. into :year from sashelp.rent;

Or you could just remove the spaces after creating the macro variable with the spaces.

select year(max(date)) into :year from sashelp.rent;
%let year=&year;
user7NPk0
Related