topic Re: How to generate daily date series from start? in SAS Programming

How to generate daily date series from start?

mariamon0 — Tue, 28 Apr 2020 15:31:33 GMT

I have a "start date" and I want to creates a series of daily dates from that date for each individual person in my code.

How do I do this?

Re: How to generate daily date series from start?

andreas_lds — Tue, 28 Apr 2020 15:38:54 GMT

Maybe by using a loop in a data step, if your date is a sas-date. Posting example data of what you have and showing what you expect as result will help us to help you.

Re: How to generate daily date series from start?

Jagadishkatam — Tue, 28 Apr 2020 15:41:54 GMT

please try the below code untested code , I am assuming that the start_date is numeric date

data want;
set have;
do i = start_date to today();
output;
end;
run;

Re: How to generate daily date series from start?

Reeza — Tue, 28 Apr 2020 16:59:33 GMT

If you have SAS/ETS license (check using PROC PRODUCT_STATUS) you can use the TIMESERIES procedure to fill the missing dates and impute the values if you're so inclined. You can set it to carry the last observation forward or assign it 0 or missing.

Here's an example where I fill in missing months (after I delete them in the first step for testing).

/*1*/
data ibm;
	set sashelp.stocks;
	where stock='IBM';

	if month(date)=7 then
		delete;
run;

proc sort data=ibm;
	by date;
run;

/*2*/
proc timeseries data=ibm out=ibm_no_missing;
	id date interval=month start='01Aug1986'd end='01Dec2005'd;
	var open;
run;

Re: How to generate daily date series from start?

ghosh — Tue, 28 Apr 2020 18:38:30 GMT

Just change the start and end dates. Just noticed @Reeza's comment, edited to include a persons list, then merged the two files

/* Specify start and end dates*/
%let start_date=%str(1JAN2020);
%let end_date=%str(1APR2020);
data daily;
	date_N="&start_date"d;
	do while (date_N<="&end_date"d);
		output;
		date_N=intnx('day', date_N, 1, 's');
	end;
	format date_N date9.;
run;

data persons;
input name $;
cards;
John
Mary
Sally
Harry
;
proc sql;
  create table comb as 
    select * from persons,daily
      order by name;
 quit;

Re: How to generate daily date series from start?

Reeza — Tue, 28 Apr 2020 18:21:38 GMT

None of the solutions so far factor in your individual person/date. One option is to generate the date list and a unique person list and cross join them as well.

If you're using the TIMESERIES approach you could just add a BY statement to the code for each individual and it would fill it in.

Re: How to generate daily date series from start?

mariamon0 — Wed, 29 Apr 2020 12:38:19 GMT

I am definitely confused. Is there a way to do that with the intnx function? How would I do it with time series.

data web.finalgoal;

set web.goaldata;

randomization_date=start_date;

I dont have a variable for end_date do I need to create one?

by study_id;

Re: How to generate daily date series from start?

ghosh — Wed, 29 Apr 2020 13:25:29 GMT

I have broken this down into steps for you. Assuming randomization_date is numerical date values.

Untested code for your time series

proc sql;
select min(randomization_date),max(randomization_date) 
	into :startdate, :enddate
		from web.goaldata;
quit;
data daily;
	date_N="&start_date"d;
	do while (date_N<="&end_date"d);
		output;
		date_N=intnx('day', date_N, 1, 's');
	end;
	format date_N date9.;
run;
proc sql;
     create table studies as 
  select distinct study_id from  web.goaldata;
quit;
proc sql;
  create table web.finalgoal as 
    select * from studies,daily
      order by studies;
 quit;