Using call symputx in a macro program

[robulon]

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

[gamotte · Accepted solution]

Hello,

Try :

proc sql noprint;
    SELECT strip(put(max(run_date),weekdatx.))
    INTO : &table._mrd_d
    FROM &table._chk;
quit;

[Kurt_Bremser]

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.

[robulon]

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.

[gamotte]

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.

[Tom]

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 ' '

[Tom]

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;

[robulon]

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.