Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

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

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:

 

TPMCPWCPWSCSiteETDateTimeDIAMPXMCSF
71017101US0005211Works108Nov201611:58890,3 1
71027102US0003611Works202Nov201613:01878,1 1
71027102UC0003482Works307Nov201618:22877,3 1
71067106UC004241Works105Oct20169:43890,4 1
71067106UC004373Works207Nov201618:23877,1 1
71067106UC3094Works307Nov201618:26877,8 1
71077107UC053271Works106Oct20168:41837 1
71077107UC2002Works213Oct201612:53890,55 1
71087108UC0003613Works102Nov201613:01878,1 1
71087108UC004321Works207Nov201618:25877,8 1
71087108UC1062Works303Oct20169:37890,3 1

 

 

So the Output should look like this:

 

TPMCPWCPWSCSiteETDateTimeDIAMPXMCSF
     03Nov2016    
     04Nov2016    
     05Nov2016    
     06Nov2016    
     07Nov2016    
71017101US0005211Works108Nov201611:58890,3 1
     09Nov2016    
     10Nov2016    
     11Nov2016    
     12Nov2016    
     13Nov2016    
     28Oct2016    
     29Oct2016    
     30Oct2016    
     31Oct2016    
     01Nov2016    
71027102US0003611Works102Nov201613:01878,1 1
     03Nov2016    
     04Nov2016    
     05Nov2016    
     06Nov2016    
     07Nov2016    

 

and so forth.


Accepted Solutions
Solution
‎11-14-2016 05:51 AM
Grand Advisor
Posts: 9,567

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


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;


View solution in original post


All Replies
Esteemed Advisor
Posts: 6,636

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

[ Edited ]

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.

 

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎11-14-2016 05:51 AM
Grand Advisor
Posts: 9,567

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


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;


Frequent Contributor
Posts: 87

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

Hi

 

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:

 

TPMCPWCPWSCSiteETDateTimeDIAMPXMCSF
7101    03Nov20160:00   
7101    04Nov20160:00   
7101    05Nov20160:00   
7101    06Nov20160:00   
7101    07Nov20160:00   
71017101US0005211Works108Nov201611:58890,3 1
7101    09Nov20160:00   
7101    10Nov20160:00   
7101    11Nov20160:00   
7101    12Nov20160:00   
7101    13Nov20160:00   
7102    28Oct20160:00   
7102    29Oct20160:00   
7102    30Oct20160:00   
7102    31Oct20160:00   
7102    01Nov20160:00   
71027102US0003611Works102Nov201613:01878,1 1
7102    03Nov20160:00   
7102    04Nov20160:00   
7102    05Nov20160:00   
7102    06Nov20160:00   
7102    07Nov20160:00   
Grand Advisor
Posts: 9,567

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

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;
Frequent Contributor
Posts: 87

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

[ Edited ]

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)Smiley SadColumn).
      20:13   
NOTE: Numeric values have been converted to character values at the places given by: (Line)Smiley SadColumn).
      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:

 

TopParentMachCodeMachCodeEventTypeDateTimeSiteDiameterParentXMachCodeMileageValueservice_flag
 US000328Lathing02JAN160:00 885.9 123124.121
 US000328Lathing13JUN160:001881.4LI1147P3213.431
 US000329Lathing14JAN160:00 872.6 342424.121
 US000329Lathing05AUG160:00 863.1  1
7114US000331Installation19APR160:003.LI1147P 1
 US000331Lathing12JUN160:00 860.7 12313.541
 US000334Lathing24JAN160:00 853.2 212122.451
Esteemed Advisor
Posts: 6,636

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Grand Advisor
Posts: 9,567

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

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;

Frequent Contributor
Posts: 87

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

Yes this is correct. I got my mistake. Thanks for correcting. I got to learn a lot from this. and Finally this solution worked! Smiley Happy
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 290 views
  • 2 likes
  • 3 in conversation