Use macro variable in macro function to filter data

Question

I want to use a macro variable in a macro function to filter dataset based on date with a proc sql statement.

My macro variable is created like this;

data _null_;
do i = 1 to 2;
 call symputx(cats('start_date_', i), cats(2022+i, "-01-01"), "G");
 call symputx(cats('end_date_', i),  cats(2022+i, "-12-31"), "G");
end;
run;

So start_date_1 is 2023-01-01 so on.

Them I use this in a loop to create datasets.

%macro getdata(start, end);

proc sql; 
 create table sometable as 
 select * 
 from mydataset
 where keydate between &start and &end;
quit;

%mend getdata; 

I have replated the &start in proc sql with "&sstart"d, %tslit(&start) and similar tricks, but no luck.

When I looked at samnple of data, the key date looks to be in date9. format as in 08FEN2022.

Answer 1

The fundamental is that the keydate is a SAS date whereas your macro variable are not. 2023-01-01 is not a valid SAS date. "01JAN2023"d is.

Therefore, you could change your data step to create macro variables that have the correct format.

data _null_;
do i = 1 to 2;
    call symputx(cats('start_date_', i), put(intnx('year', "01JAN2022"d, i, 's'), date9.), "G");
    call symputx(cats('end_date_', i),  put(intnx('year', "31DEC2022"d, i, 's'), date9.), "G");
end;
run;

%put &=start_date_1 &=end_date_1;
%put &=start_date_2 &=end_date_2;

START_DATE_1=01JAN2023 END_DATE_1=31DEC2023
START_DATE_2=01JAN2024 END_DATE_2=31DEC2024

then wrap around the macro vriables with a double quote (not single ones otherwise macro is not resolved) and add a constant d that tells SAS to interpret the data in quotes as a date.

%macro getdata(start, end);
    proc sql;
        create table sometable as 
        select * 
        from have
        where keydate between "&start"d and "&end"d;
    quit;
%mend getdata;

%getdata(start=&start_date_1, end=&end_date_1);
%getdata(start=&start_date_2, end=&end_date_2);