Abridged sample of my data below:
ACCOUNT, DATE
12345, 9/25/20
74156, 9/26/20
57831, 9/26/20
47856, 9/27/20
45178, 9/27/20
23654, 9/28/20
87654, 9/29/20
56789, 9/29/20
54321, 9/30/20
I would like to extract from this (sample) dataset all entries that occur between 9/26/20 to 9/29/20.
However - I would like a separate output for each date. If I used PROC SQL for example this is what my query would look like:
PROC SQL;
create table Sep26 as select * from DATA where date GE 9/26/20 and date LT 9/27/20;
quit;
PROC SQL;
create table Sep27 as select * from DATA where date GE 9/27/20 and date LT 9/28/20;
quit;
PROC SQL;
create table Sep28 as select * from DATA where date GE 9/28/20 and date LT 9/29/20;
quit;
PROC SQL;
create table Sep29 as select * from DATA where date GE 9/29/20 and date LT 9/30/20;
quit;
I would thus like just one query that loops over date range 9/26/20 to 9/29/20 and produces individual output for each date. I would imagine I need a macro variable to accomplish this?
data have;
infile cards dsd;
input ACCOUNT DATE :mmddyy10.;
format date mmddyy10.;
cards;
12345, 9/25/20
74156, 9/26/20
57831, 9/26/20
47856, 9/27/20
45178, 9/27/20
23654, 9/28/20
87654, 9/29/20
56789, 9/29/20
54321, 9/30/20
;
/*Define your logic in a macro definition*/
%macro t;
/*Declare your interval boundaries*/
%let start=%sysevalf('26sep2020'd);
%let end=%sysevalf('29sep2020'd);
%do i=&start %to &end;
proc sql;
create table Mon_%sysfunc(putn(&i,date9.)) as
select *
from have
where date=&i;
quit;
%end;
%mend t;
%t /*call and execute the macro*/
You don't need anything, because you don't do it. WHERE conditions and BY-group processing is all you need.
data have;
infile cards dsd;
input ACCOUNT DATE :mmddyy10.;
format date mmddyy10.;
cards;
12345, 9/25/20
74156, 9/26/20
57831, 9/26/20
47856, 9/27/20
45178, 9/27/20
23654, 9/28/20
87654, 9/29/20
56789, 9/29/20
54321, 9/30/20
;
/*Define your logic in a macro definition*/
%macro t;
/*Declare your interval boundaries*/
%let start=%sysevalf('26sep2020'd);
%let end=%sysevalf('29sep2020'd);
%do i=&start %to &end;
proc sql;
create table Mon_%sysfunc(putn(&i,date9.)) as
select *
from have
where date=&i;
quit;
%end;
%mend t;
%t /*call and execute the macro*/
Thank you
Exactly what I was after. Much appreciated.
If you're going to do what you asked, take a look at the hash method as shown in the following presentation: https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/TASS/Tabachneck-SASTips-2011....
HTH,
Art
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.