How to put date column into macro variable SAS

Question

I have a dataset that has 2 date columns.The date columns in dataset1 are formatted like "01/01/2024"

I put them both into a macro variable like this:-

%if &DateType. = date1 %then %do;

proc sql no print;
select date1 into: date separated by ', '
from dataset1;
quit;

%end;
%else %if &DateType. = date2 %then %do;

proc sql no print;
select date2into: date separated by ', '
from dataset1;
quit;
%end;

I want to use this macro variable to use in a where statement:-

proc sql;
create table want as
select *
from dataset1
where &date. > '01Dec2028'd
;
quit;

However, I keep getting the "ERROR: Syntax error, expecting one of the following.." in my macro valuable. Do I need to change the format?

Answer 1

If your date resolves to more than one date, you'll be passing in a comma-separated list of numeric dates. In other words, &date may resolve to:

proc sql;
create table want as
select *
from dataset1
where 23330,23331 > '01Dec2028'd
;
quit;

You will need to select a single date from your table from dataset1. You can do this by selecting the max date or min date. Additionally, if no valid dates exist in dataset1, it could also pass a missing value to it, so you'll need to verify that a nonmissing date exists within the table. For example:

proc sql no print;
select max(date1) into: date
from dataset1
where NOT missing(date1)
quit;

%if(&sqlobs = 0) %then %do;
    %put ERROR: No valid dates exist in DATASET1.;
    %abort;
%end;