topic Re: Using FOR LOOPS to query a dataset multiple times by date in SAS Programming

Using FOR LOOPS to query a dataset multiple times by date

wylie_ma — Mon, 05 Oct 2020 22:10:48 GMT

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?

Re: Using FOR LOOPS to query a dataset multiple times by date

Reeza — Mon, 05 Oct 2020 22:20:41 GMT

This is rarely useful, so it's usually not recommended in SAS especially since BY group processing within data steps is very efficient.

If you really want a macro look into this or CALL EXECUTE().
https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Re: Using FOR LOOPS to query a dataset multiple times by date

Kurt_Bremser — Mon, 05 Oct 2020 22:26:03 GMT

You don't need anything, because you don't do it. WHERE conditions and BY-group processing is all you need.

Re: Using FOR LOOPS to query a dataset multiple times by date

novinosrin — Mon, 05 Oct 2020 23:23:24 GMT

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*/

Re: Using FOR LOOPS to query a dataset multiple times by date

art297 — Mon, 05 Oct 2020 23:25:29 GMT

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.pdf

HTH,

Art

Re: Using FOR LOOPS to query a dataset multiple times by date

wylie_ma — Tue, 06 Oct 2020 02:34:13 GMT

Thank you

Exactly what I was after. Much appreciated.