%qcmpres macro leaving one trailing blank in

[robulon]

Hi,

I'm just being lazy here but I'm trying to find a way to use &syslast in conjunction with a macro date variable (called dm_mrd_d) to name a data set that I am exporting to Excel in Base SAS 9.4. Initially when was doing this, I was finding 16 trailing blanks between the &syslast macro variable and the &dm_mrd_d macro variable.

Having done some investigation, I found out about the %qcmpres macro which I hoped would let me achieve what I was trying to. My code now reads: -

proc export data= &syslast

outfile= "\\appsl\users\rob\%qcmpres(&syslast._&dm_mrd_d.).xls"

dbms=excel replace;

run;

and the macros resolve to: -

WORK.WORK_PHONES_ONLY _20180316

I'd like to get rid of the last trailing blank so the file that is created has no blanks in the name but am not sure how. When I check the log with SYMBOLGEN on, I see the following: -

SYMBOLGEN: Macro variable I resolves to 22

This is the length of the filename plus one trailing blank so I think this is the point at which I need to remove that but just not sure how.

Hopefully I've posted enough information but if any more is needed, please let me know.

Thanks,

Rob

[Kurt_Bremser · Accepted solution]

Build the filename in a data step and use trim() to get rid of any trailing blanks:

data _null_;
outfile = trim("&syslast.") !! trim("_&dm_mrd_d.") !! '.xls');
call symputx('outfile',outfile);
run;

And use &outfile in the proc export statement.

[robulon]

Brilliant, thanks Kurt

[Kurt_Bremser]

I have to admit that it is quite a crude method, but it works. I follow my Maxim 29.

[Ksharp]

outfile= "\\appsl\users\rob\%trim(&syslast.)%left(_&dm_mrd_d.).xls"

[Tom]

The blanks on the end of SYSLAST are not macro quoted. So just assigning the value to another macro variable will remove them.

%let dsn=&syslast ;

proc export data= &dsn
  outfile= "\\appsl\users\rob\&dsn._&dm_mrd_d..xls"
  dbms=excel replace
;
run;