DATA Step, Macro, Functions and more

%qcmpres macro leaving one trailing blank in

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

%qcmpres macro leaving one trailing blank in

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

 


Accepted Solutions
Solution
‎03-19-2018 06:00 AM
Super User
Posts: 9,919

Re: %qcmpres macro leaving one trailing blank in

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎03-19-2018 06:00 AM
Super User
Posts: 9,919

Re: %qcmpres macro leaving one trailing blank in

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 22

Re: %qcmpres macro leaving one trailing blank in

Posted in reply to KurtBremser
Brilliant, thanks Kurt
Super User
Posts: 9,919

Re: %qcmpres macro leaving one trailing blank in

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,691

Re: %qcmpres macro leaving one trailing blank in

outfile= "\\appsl\users\rob\%trim(&syslast.)%left(_&dm_mrd_d.).xls"
Super User
Super User
Posts: 7,936

Re: %qcmpres macro leaving one trailing blank in

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;
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 83 views
  • 0 likes
  • 4 in conversation