Repeat SAS code each time with successive date and within a date range

[anandas]

Hello

I am running the following code to get the table I want. I have attached the table I "have" (sample records) and the table I "want" with this message. 

proc sql;
create table cohort as select *
from source.table 
where term='201602' and
	rsts_date<='16OCT2015:02:00:00'dt and
	activity_date<='16OCT2015:02:00:00'dt;
quit;

data cohort1;
set cohort;
length code$ 25.;
code=id||crn;
proc sort; by code descending seq_number;

data cohort2;
set cohort1;
by code;
if first.code;
if rsts_code not in ('RE','RW','RC','AU','CT','RX') then delete;
if error_flag='F' then delete;
if substr(message,1,14)='Record deleted' then delete;
run;

proc sql;
create table cohort3 as select distinct term,date,sum(credits) as credits format=comma8.
from cohort2
group by date;
quit;

proc datasets nolist; 
append base=final data=cohort3 force;
quit;

To get the 'want' table that I have attached, i run the above script manually each time with successive dates. 

My question: Can I create a new script using the logic above to automatically repeat the code enough times to reach the end of the date range. So currently, the date range I want is from Oct 15 2015 to Feb 15 2016. So I need to run this script for each date starting from Oct 15 2016, then Oct 16 2016, then Oct 17 2016...up to Feb 15 2016. In my "want" table, I have only shown two dates that I ran manually as an example. I want this to run repeatedly grouping the data by date. There are 124 days between oct 15 2015 and feb 15 2016 so I would need a table with 124 rows.

Hope I am making sense.

Thanks in advance

ananda

[PaigeMiller]

This is easily done via a macro. But it may also be done without a macro, where you pull the records for all dates at once from source.table and then using BY groups and DATA step logic, you can probably avoid using macros at all. Any solution using no macros at all is what I recommend.

Some of us will not donwload Excel files, so I cannot see your "want" table, and a screen capture would help.

Please provide the "have" table as SAS data step code (instructions)

[Reeza]

See the second link on how to convert your code to macro code but I'll echo @PaigeMiller suggestion of using BY group processing which may be more efficient.

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[Kurt_Bremser]

Post example data in data steps with datalines, as was shown here in an answer to one of your previous questions.

[ballardw]

You say " So I need to run this script for each date starting from Oct 15 2016, then Oct 16 " but your code shows:

rsts_date<='16OCT2015:02:00:00'dt and

Which only includes part of Oct 16.

So why are you using datetime values if you want a script that runs for dates?

[anandas]

I have attached the data step for the Have table and the snapshot for the Want table;

data have;
input id crn $ seq_number $ rsts_code $ rsts_date :datetime20. credits;
format rsts_date datetime20.;
datalines;
1	23224	3	RW	14OCT2015:09:35:35	3
1	23224	4	RW	14OCT2015:09:35:35	3
1	23224	5	DW	14OCT2015:10:48:55	1
1	23224	6	DW	14OCT2015:10:48:55	1
1	23224	7	DW	14OCT2015:10:48:55	1
1	24800	8	RW	14OCT2015:10:50:29	3
1	24800	9	RW	14OCT2015:10:50:29	3
1	28636	1	RW	14OCT2015:09:09:19	3
1	28636	2	RW	14OCT2015:09:09:19	3
2	20593	2	RW	14OCT2015:11:04:07	4
2	20593	3	RW	14OCT2015:11:04:07	4
2	20593	5	DW	14OCT2015:11:12:43	1
2	20593	6	DW	14OCT2015:11:12:43	1
2	20593	7	DW	14OCT2015:11:12:43	1
2	20594	11	RW	14OCT2015:11:20:12	4
2	20594	12	RW	14OCT2015:11:20:12	4
3	20361	1	RW	14OCT2015:19:18:55	4
3	20361	4	RW	14OCT2015:19:18:55	4
3	20362	2	RW	14OCT2015:19:18:55	4
3	20362	5	RW	14OCT2015:19:18:55	4
3	20364	10	RW	14OCT2015:19:21:14	3
3	20364	11	RW	14OCT2015:19:21:14	3
3	24770	3	RW	14OCT2015:19:18:55	3
3	24770	6	RW	14OCT2015:19:18:55	3
3	24770	7	DW	14OCT2015:19:20:50	1
3	24770	8	DW	14OCT2015:19:20:50	1
3	24770	9	DW	14OCT2015:19:20:50	1
4	20102	5	RW	14OCT2015:15:22:28	3
4	20102	6	RW	14OCT2015:15:22:28	3
4	23217	1	RW	14OCT2015:15:20:14	3
4	23217	2	RW	14OCT2015:15:20:14	3
4	23952	3	RW	14OCT2015:15:21:13	1
4	23952	4	RW	14OCT2015:15:21:13	1
13	20935	1	RE	15OCT2015:11:56:42	4
13	20941	2	RE	15OCT2015:11:56:49	3
13	21615	3	RE	15OCT2015:11:56:45	3
13	22327	4	RE	15OCT2015:11:56:53	3
14	21201	3	RW	15OCT2015:21:12:46	4
14	21201	4	RW	15OCT2015:21:12:46	4
14	23195	1	RW	15OCT2015:21:07:03	5
14	23195	2	RW	15OCT2015:21:07:03	5
14	24033	5	RW	15OCT2015:21:21:13	4
18	21045	1	RW	15OCT2015:14:34:23	4
18	21045	2	RW	15OCT2015:14:34:23	4
18	22444	3	RW	15OCT2015:14:36:57	4
18	22444	4	RW	15OCT2015:14:36:57	4
18	22444	5	DW	15OCT2015:14:36:57	4
18	22444	6	DW	15OCT2015:14:36:57	4
;
run;

WANT

This is just a sample table to keep things simple. Any help with the code will be greatly appreciated. I am looking to run this for each date from Oct 15 2015 to Feb 15 2016. 

Thanks. 

[anandas]

I am sorry about the multiple posts but forgot to mention. This is a transactional table and the values change every day. So I have to run this daily to get the snapshot by the day. Thanks.