topic Re: Using call symputx in a macro program in SAS Programming

Using call symputx in a macro program

robulon — Wed, 04 Apr 2018 11:01:30 GMT

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

Re: Using call symputx in a macro program

gamotte — Wed, 04 Apr 2018 11:59:24 GMT

Hello,

Try :

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

Re: Using call symputx in a macro program

Kurt_Bremser — Wed, 04 Apr 2018 12:10:08 GMT

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.

Re: Using call symputx in a macro program

robulon — Wed, 04 Apr 2018 12:15:09 GMT

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.

Re: Using call symputx in a macro program

Tom — Wed, 04 Apr 2018 12:17:49 GMT

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;

Re: Using call symputx in a macro program

gamotte — Wed, 04 Apr 2018 12:42:39 GMT

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.

Re: Using call symputx in a macro program

robulon — Wed, 04 Apr 2018 12:45:09 GMT

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.

Re: Using call symputx in a macro program

Tom — Wed, 04 Apr 2018 12:48:26 GMT

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