topic Re: how to put proc sql select into macro variable name in SAS Enterprise Guide

how to put proc sql select into macro variable name

alepage — Mon, 09 Aug 2021 15:33:37 GMT

Hello,

I have using RealDate to put the value of transaction_dt in it.

But it there a way to replace the name of RealDate by the value of macro variable name.

%let cie5=wu;
%let lob2=prop;

%let name=RealDate_&cie5._&lob2;
%put &=name.;

proc sql noprint;
select transaction_dt into : RealDate
from (select transaction_dt,
count(transaction_dt) as nobservations
from srch5.&cie5.&lob2._prm_d&date1.
group by transaction_dt)
having nobservations=max(nobservations);
quit;

%put ===========> &=RealDate;

ex:

proc sql ;

select into : &name.

...

quit;

Re: how to put proc sql select into macro variable name

PaigeMiller — Mon, 09 Aug 2021 16:44:31 GMT

But it there a way to replace the name of RealDate by the value of macro variable name.

I'm really not understanding the above sentence. Please explain further. Show us what you want in pseudo-code.

Re: how to put proc sql select into macro variable name

alepage — Mon, 09 Aug 2021 17:31:03 GMT

Hello, I am sorry for not being clear.

I will do my best.

if you look a the proc sql query below, it put the maximum transaction_dt into RealDate. But if you look at the from statement,

srch5 is a libname, &cie5, is a macro variable containing a precise company abreviation (ex: Lo for Loyds, and lob2 is a macro variable containing the line of business, prop for property.

So I would like instead of using Realdate as the macro variable name into the proc sql statement to use something more personnal like RealDate_&cie5._&lob2. so the macro variable name become RealDate_Lo_prop.

Re: how to put proc sql select into macro variable name

Tom — Mon, 09 Aug 2021 17:49:08 GMT

to use something more personal like RealDate_&cie5._&lob2. so the macro variable name become RealDate_Lo_prop.

So did you try that? What happened?

select transaction_dt
  into :RealDate_&cie5._&lob2.
  from 
  ( select transaction_dt
          , count(transaction_dt) as nobservations
   from srch5.&cie5.&lob2._prm_d&date1.
  group by transaction_dt
  )
  having nobservations=max(nobservations)
;

To reference that new name use something like:

%put &&RealDate_&cie5._&lob2.

Although I find it is often easier to build the name into another macro variable and then use that.

%let mvar=RealDate_&cie5._&lob2.;
select transaction_dt
  into :&mvar
...
%put &&&mvar;

Re: how to put proc sql select into macro variable name

alepage — Wed, 11 Aug 2021 12:23:20 GMT

We are pretty close to what I was looking for.

As I can see, from your example, it is not with the proc SQL that I have difficulties but with the %put statement.

I have tried this put statement. Usually, we just need to do %put &=mvar; to get this into the log file: RealDate_je_auto=20210729, but it is not working.

I have tried this %put &&mvar=&&&mvar;
RealDate_je_auto=20210729

Is there a better way to obtain RealDate_je_auto=20210729 into the log file.

Thanks in advance for your help, it is very appreciated.