Hi,
I have tried to do some research on this before posting my question but am struggling to locate the answer. Basically, I am creating a global macro variable within a macro program which is a date in weekdatx, format. I want to left align the variable, simply so it looks better when I reference it in the log but am struggling to do this. I can't include the whole macro program but the relevant part is: -
proc sql noprint;
select
max(run_date)
into: &table._mrd
from &table._chk;
quit;
proc sql noprint;
select
max(run_date) format weekdatx.
into: &table._mrd_d
from &table._chk;
quit;
data _null_;
&table._mrd_d = left("&table._mrd_d");
call symputx("&table._mrd_d",&table._mrd_d,'G');
run;
%mend checkdates;
%checkdates(dialler.mort_uip_dialler_list_&month,dm,MSP)
%put Maximum date on DM is &dm_mrd;
%put Maximum date on DM is &dm_mrd_d;
and my log shows this: -
1138 %put Maximum date on DM is &dm_mrd;
Maximum date on DM is 21278
1139 %put Maximum date on DM is &dm_mrd_d;
Maximum date on DM is dm_mrd_d
I'm satisfied that the macro variable is being created in SQL correctly as when I run the code without the symputx part, my log shows: -
1195 %put Maximum date on DM is &dm_mrd;
Maximum date on DM is 21278
1196 %put Maximum date on DM is &dm_mrd_d;
Maximum date on DM is Wednesday, 4 April 2018
So it is just the symputx part where my coding is wrong. Would anyone be able to advise where I'm going wrong?
Many thanks,
Rob
Hello,
Try :
proc sql noprint;
SELECT strip(put(max(run_date),weekdatx.))
INTO : &table._mrd_d
FROM &table._chk;
quit;
Hello,
Try :
proc sql noprint;
SELECT strip(put(max(run_date),weekdatx.))
INTO : &table._mrd_d
FROM &table._chk;
quit;
if you just want to remove leading and trailing blanks from a macro variable, do this:
%let &table._mrd_d = &&table._mrd_d;
Please show the whole log of the macro call. Use the {i} button for posting code. Also who the whole macro code, the %macro definition is missing.
Thanks gamotte and Kurt. I was reluctant to put the whole macro code in as it is a work piece that references file names that it would be frowned upon if I were to share and I would have needed to change a significant amount of it in order to publish it.
As always really appreciate members taking the time to look and respond to my questions so many thanks to you both.
It is more about providing a full minimal working code that can reproduce the problem without
requiring the users to create their own test datasets or to guess and complete the missing parts.
The exact names of variables and datasets are unimportant as long as they are not part of the problem.
The kludge for older versions of SAS (10 years ago?) was to use the SEPARATED BY keyword. Just make sure that your query returns only one observation.
into :mvar separated by ' '
If you want to make the macro variable from PROC SQL without the leading/trailing spaces you can use the TRIMMED keyword in the INTO clause.
proc sql noprint;
select max(run_date)
, max(run_date) format weekdatx.
into :&table._mrd trimmed
, :&table._mrd_d trimmed
from &table._chk
;
quit;
Thanks Tom,
One of the things I love about SAS is that there are so many different ways to get to the right end result.
The trimmed keyword doesn't work for me at work but I think that is because I use an older version of SAS. I'll certainly give it a go on SAS Studio when I get home though.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: