[SOLVED] Pass select distinct max(some field) as a variable

Pass select distinct max(some field) as a variable

Is there a way to pass the results of a simple query, used to get the maximum date of column, as a variable? Currently I run the query then just copy/paste the output into the variable.

select distinct max(month) as max_month FORMAT=MMDDYY10. 
from library.Table1

Then I just drop the value into:

%let max_month = '28Feb2025'd;

Solution

If you want to create a VARIABLE it has to be in a DATASET.

create table table1_max_month as
select max(month) as max_month FORMAT=MMDDYY10. 
from library.Table1
;

If instead you just want to store the value as text into a macro variable so you can use it to later generate some code then use the INTO clause of PROC SQL.

proc sql noprint;
select max(month)
  into :max_month trimmed
from library.Table1
;
quit;

You can then use that macro variable later where you want the maximum value of the MONTH varaible.

data want;
  set table2 ;
  where date > &max_month;
run;

Note that for this usage you do NOT want to apply a format to the calculated MAX(month). Instead just let PROC SQL create a digit string that will represent the number of days since 1960 that SAS uses to represent date values.

And if you need the MAX() value in the same dataset then just let PROC SQL automatically remerge the value onto every observation.

create table want as 
select id
     , max(month) as max_month FORMAT=MMDDYY10.
     , month , calculated max_month - date as days_till_max_month 
from library.Table1
;