how to put proc sql select into macro variable name

[alepage]

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;

[PaigeMiller]

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.

[alepage]

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.

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;

[Tom · Accepted solution]

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;

[alepage]

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.