topic Re: How can I add 5 days before and 5 days after the dates in my dataset in SAS Enterprise Guide

How can I add 5 days before and 5 days after the dates in my dataset

imanojkumar1 — Mon, 14 Nov 2016 09:54:08 GMT

I want to add addional rows with empty data in all the columns except for Date column, which should get dates 5 days before and 5 days after the each Date in the following dataset:

TPMC	PWC	PWSC	Site	ET	Date	Time	DIAM	PXMC	SF
7101	7101	US000521	1	Works1	08Nov2016	11:58	890,3		1
7102	7102	US000361	1	Works2	02Nov2016	13:01	878,1		1
7102	7102	UC000348	2	Works3	07Nov2016	18:22	877,3		1
7106	7106	UC00424	1	Works1	05Oct2016	9:43	890,4		1
7106	7106	UC00437	3	Works2	07Nov2016	18:23	877,1		1
7106	7106	UC309	4	Works3	07Nov2016	18:26	877,8		1
7107	7107	UC05327	1	Works1	06Oct2016	8:41	837		1
7107	7107	UC200	2	Works2	13Oct2016	12:53	890,55		1
7108	7108	UC000361	3	Works1	02Nov2016	13:01	878,1		1
7108	7108	UC00432	1	Works2	07Nov2016	18:25	877,8		1
7108	7108	UC106	2	Works3	03Oct2016	9:37	890,3		1

So the Output should look like this:

TPMC	PWC	PWSC	Site	ET	Date	Time	DIAM	PXMC	SF
					03Nov2016
					04Nov2016
					05Nov2016
					06Nov2016
					07Nov2016
7101	7101	US000521	1	Works1	08Nov2016	11:58	890,3		1
					09Nov2016
					10Nov2016
					11Nov2016
					12Nov2016
					13Nov2016
					28Oct2016
					29Oct2016
					30Oct2016
					31Oct2016
					01Nov2016
7102	7102	US000361	1	Works1	02Nov2016	13:01	878,1		1
					03Nov2016
					04Nov2016
					05Nov2016
					06Nov2016
					07Nov2016

and so forth.

Re: How can I add 5 days before and 5 days after the dates in my dataset

Kurt_Bremser — Mon, 14 Nov 2016 10:07:40 GMT

You will get a multitude of completely identical observations (same date, all other variables missing). These are usually a major PITA to work with.

I'd rather have at least another column (TPMC,PWC?) populated so you can build groups and preserve the correct order.

Re: How can I add 5 days before and 5 days after the dates in my dataset

Ksharp — Mon, 14 Nov 2016 10:23:18 GMT


data have(index=(date));
infile cards truncover expandtabs;
input TPMC	PWC	PWSC $	Site	ET	$ Date : date9.	Time $ DIAM	$ PXMC	$ SF;
format date date9.;
cards;
7101	7101	US000521	1	Works1	08Nov2016	11:58	890,3	 	1
7102	7102	US000361	1	Works2	02Nov2016	13:01	878,1	 	1
7102	7102	UC000348	2	Works3	07Nov2016	18:22	877,3	 	1
7106	7106	UC00424	1	Works1	05Oct2016	9:43	890,4	 	1
7106	7106	UC00437	3	Works2	07Nov2016	18:23	877,1	 	1
7106	7106	UC309	4	Works3	07Nov2016	18:26	877,8	 	1
7107	7107	UC05327	1	Works1	06Oct2016	8:41	837	 	1
7107	7107	UC200	2	Works2	13Oct2016	12:53	890,55	 	1
7108	7108	UC000361	3	Works1	02Nov2016	13:01	878,1	 	1
7108	7108	UC00432	1	Works2	07Nov2016	18:25	877,8	 	1
7108	7108	UC106	2	Works3	03Oct2016	9:37	890,3	 	1
;
run;

data want;
 set have;
 array x{1} _temporary_;
 x{1}=date;
 call missing(of _all_);
 do i=5 to 1 by -1;
   date=x{1}-i;output;
 end;
 set have point=_n_;output;
 call missing(of _all_);
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;

Re: How can I add 5 days before and 5 days after the dates in my dataset

imanojkumar1 — Mon, 14 Nov 2016 11:12:46 GMT

Just a quick question.

If I want TPMC value to be repeated in those rows where 5 before dates and 5 after dates are, how can we do it? Also instead of empty cell, for Time, can it be 00:00 ?

Something like this as end result:

TPMC	PWC	PWSC	Site	ET	Date	Time	DIAM	PXMC	SF
7101					03Nov2016	0:00
7101					04Nov2016	0:00
7101					05Nov2016	0:00
7101					06Nov2016	0:00
7101					07Nov2016	0:00
7101	7101	US000521	1	Works1	08Nov2016	11:58	890,3		1
7101					09Nov2016	0:00
7101					10Nov2016	0:00
7101					11Nov2016	0:00
7101					12Nov2016	0:00
7101					13Nov2016	0:00
7102					28Oct2016	0:00
7102					29Oct2016	0:00
7102					30Oct2016	0:00
7102					31Oct2016	0:00
7102					01Nov2016	0:00
7102	7102	US000361	1	Works1	02Nov2016	13:01	878,1		1
7102					03Nov2016	0:00
7102					04Nov2016	0:00
7102					05Nov2016	0:00
7102					06Nov2016	0:00
7102					07Nov2016	0:00

Re: How can I add 5 days before and 5 days after the dates in my dataset

Ksharp — Tue, 15 Nov 2016 02:09:10 GMT

Sure.


data have(index=(date));
infile cards truncover expandtabs;
input TPMC	PWC	PWSC $	Site	ET	$ Date : date9.	Time : hhmmss5. DIAM	$ PXMC	$ SF;
format date date9. time hhmm5.;
cards;
7101	7101	US000521	1	Works1	08Nov2016	11:58	890,3	 	1
7102	7102	US000361	1	Works2	02Nov2016	13:01	878,1	 	1
7102	7102	UC000348	2	Works3	07Nov2016	18:22	877,3	 	1
7106	7106	UC00424	1	Works1	05Oct2016	9:43	890,4	 	1
7106	7106	UC00437	3	Works2	07Nov2016	18:23	877,1	 	1
7106	7106	UC309	4	Works3	07Nov2016	18:26	877,8	 	1
7107	7107	UC05327	1	Works1	06Oct2016	8:41	837	 	1
7107	7107	UC200	2	Works2	13Oct2016	12:53	890,55	 	1
7108	7108	UC000361	3	Works1	02Nov2016	13:01	878,1	 	1
7108	7108	UC00432	1	Works2	07Nov2016	18:25	877,8	 	1
7108	7108	UC106	2	Works3	03Oct2016	9:37	890,3	 	1
;
run;

data want;
 set have;
 array x{2} _temporary_;
 x{1}=date;x{2}=TPMC;
 call missing(of _all_);
 TPMC=x{2};Time=0;
 do i=5 to 1 by -1;
   date=x{1}-i;output;
 end;
 set have point=_n_;output;
 call missing(of _all_);
 TPMC=x{2};Time=0;
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;

Re: How can I add 5 days before and 5 days after the dates in my dataset

imanojkumar1 — Tue, 15 Nov 2016 10:32:21 GMT

Hello Ksharp,

Thanks for helping me. Please ignore the variables names and data (disclosure restrictions at office), i applied your method on my data and I got the following errors:

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
20:13
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
22:20 28:20
ERROR: Array subscript out of range at line 20 column 13.
MachCode=US0001 EventType=Lathing Date=02JAN13 Time=19:24 TopPMachCode= ParentXMachCode= Site= Diameter=876
MileageValue=2660403.00000 service_flag=1 i=. _ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.

What could be the reason?

Sample Data:

TopParentMachCode	MachCode	EventType	Date	Time	Site	Diameter	ParentXMachCode	MileageValue	service_flag
	US000328	Lathing	02JAN16	0:00		885.9		123124.12	1
	US000328	Lathing	13JUN16	0:00	1	881.4	LI1147P	3213.43	1
	US000329	Lathing	14JAN16	0:00		872.6		342424.12	1
	US000329	Lathing	05AUG16	0:00		863.1			1
7114	US000331	Installation	19APR16	0:00	3	.	LI1147P		1
	US000331	Lathing	12JUN16	0:00		860.7		12313.54	1
	US000334	Lathing	24JAN16	0:00		853.2		212122.45	1

Re: How can I add 5 days before and 5 days after the dates in my dataset

Kurt_Bremser — Tue, 15 Nov 2016 10:17:58 GMT

You failed to properly populate the variable i (see i=. in the list of values after the ERROR).

If you used i to index into the array, that's the reason for the ERROR.

And look at program lines 20, 22 and 28 to get rid of the automatic type casts. Especially line 20, where the subscript out of range also happens, will be suspicious.

That's all we can tell from here without seeing the code.

Re: How can I add 5 days before and 5 days after the dates in my dataset

Ksharp — Tue, 15 Nov 2016 10:59:17 GMT

I guess TPMC is character type variable , so you need character type array.


data want;
 set have;
 array x{1} _temporary_;
array y{1} $ 40 _temporary_;
 x{1}=date;y{1}=TPMC;
 call missing(of _all_);
 TPMC=y{1};Time=0;
 do i=5 to 1 by -1;
   date=x{1}-i;output;
 end;
 set have point=_n_;output;
 call missing(of _all_);
 TPMC=y{1};Time=0;
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;

Re: How can I add 5 days before and 5 days after the dates in my dataset

imanojkumar1 — Tue, 15 Nov 2016 11:43:36 GMT

Yes this is correct. I got my mistake. Thanks for correcting. I got to learn a lot from this. and Finally this solution worked! 🙂