Desktop productivity for business analysts and programmers

how to repeat certain values while adding dates such as n days before and n days after?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

how to repeat certain values while adding dates such as n days before and n days after?



data have; input TPMC $ PWC $ PWSC $ Site ET $ Date :date8. Time :TIME. DIAM PXMC $ SF; FORMAT DATE date8. Time HHMM.; datalines; 7101 7101 US000521 1 Works1 08Nov2016 11:58 890.3 1 7102 7102 US000361 1 Works1 02Nov2016 13:01 878.1 1 7102 7102 UC000348 2 Works1 07Nov2016 18:22 877.3 1 7106 7106 UC00424 1 Works1 05Oct2016 9:43 890.4 1 7106 7106 UC00437 3 Works1 07Nov2016 18:23 877.1 1 7106 7106 UC309 4 Works1 07Nov2016 18:26 877.8 1 7107 7107 UC05327 1 Works1 06Oct2016 8:41 837 1 7107 7107 UC200 2 Works1 13Oct2016 12:53 890.55 1 7108 7108 UC000361 3 Works1 02Nov2016 13:01 878.1 1 7108 7108 UC00432 1 Works1 07Nov2016 18:25 877.8 1 7108 7108 UC106 2 Works1 03Oct2016 9:37 890.3 1 ; run;

 

Now when I use the following SAS code:

 

data DaysBeforeAfterEvent;
 set XX;
 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 XX point=_n_; output;
 call missing(of _all_);
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;

 

 I want the result table to have repeat values and dates like this:

 

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   

Accepted Solutions
Solution
‎11-14-2016 10:13 AM
Valued Guide
Posts: 950

Re: how to repeat certain values while adding dates such as n days before and n days after?

[ Edited ]

OK, got it.  I think the best approach here is to use two SET statements, as below.  The first SET gets the date and facilitates the 5 prior and 5 trailing dates.  The second gets the event:

 

Regards,

Mark

 

Here's a corrected version:

data have;
  infile datalines truncover;
  input TPMC $ PWC $ PWSC $ Site ET $ Date :date8. Time :TIME. DIAM PXMC $ SF;
  FORMAT DATE date8. Time HHMM.;
datalines;
7101 7101 US000521 1 Works1 08Nov2016 11:58 890.3 1
7102 7102 US000361 1 Works1 02Nov2016 13:01 878.1 1
7102 7102 UC000348 2 Works1 07Nov2016 18:22 877.3 1
7106 7106 UC00424 1 Works1 05Oct2016 9:43 890.4 1
7106 7106 UC00437 3 Works1 07Nov2016 18:23 877.1 1
7106 7106 UC309 4 Works1 07Nov2016 18:26 877.8 1
7107 7107 UC05327 1 Works1 06Oct2016 8:41 837  1
7107 7107 UC200 2 Works1 13Oct2016 12:53 890.55 1
7108 7108 UC000361 3 Works1 02Nov2016 13:01 878.1 1
7108 7108 UC00432 1 Works1 07Nov2016 18:25 877.8 1
7108 7108 UC106 2 Works1 03Oct2016 9:37 890.3 1
;
run;

data want (drop=I);
  array _date{1} _temporary_;
  array _tpmc{1} $8 _temporary_;

  set have (keep=date tpmc);

  /* preceding dates */
  time='00:00:00't;
  do I=date-5 to date-1; output; end;

  /* event date */
  set have;  output;

  /* trailing dates*/
  _date{1}=date; _tpmc{1}=tpmc;
  call missing (of _all_);
  tpmc=_tpmc{1};    time='00:00:00't;
  do date=_date{1}+1 to _date{1}+5; output; end;
run;

 

View solution in original post


All Replies
Valued Guide
Posts: 950

Re: how to repeat certain values while adding dates such as n days before and n days after?

Exactly what values are being repeated in your sample result?
Frequent Contributor
Posts: 87

Re: how to repeat certain values while adding dates such as n days before and n days after?

TPMC and Time as shown in the result table.

 

So TPMC and Time (00:00) will be repeated five time above and 5 times below (along with dates).

Solution
‎11-14-2016 10:13 AM
Valued Guide
Posts: 950

Re: how to repeat certain values while adding dates such as n days before and n days after?

[ Edited ]

OK, got it.  I think the best approach here is to use two SET statements, as below.  The first SET gets the date and facilitates the 5 prior and 5 trailing dates.  The second gets the event:

 

Regards,

Mark

 

Here's a corrected version:

data have;
  infile datalines truncover;
  input TPMC $ PWC $ PWSC $ Site ET $ Date :date8. Time :TIME. DIAM PXMC $ SF;
  FORMAT DATE date8. Time HHMM.;
datalines;
7101 7101 US000521 1 Works1 08Nov2016 11:58 890.3 1
7102 7102 US000361 1 Works1 02Nov2016 13:01 878.1 1
7102 7102 UC000348 2 Works1 07Nov2016 18:22 877.3 1
7106 7106 UC00424 1 Works1 05Oct2016 9:43 890.4 1
7106 7106 UC00437 3 Works1 07Nov2016 18:23 877.1 1
7106 7106 UC309 4 Works1 07Nov2016 18:26 877.8 1
7107 7107 UC05327 1 Works1 06Oct2016 8:41 837  1
7107 7107 UC200 2 Works1 13Oct2016 12:53 890.55 1
7108 7108 UC000361 3 Works1 02Nov2016 13:01 878.1 1
7108 7108 UC00432 1 Works1 07Nov2016 18:25 877.8 1
7108 7108 UC106 2 Works1 03Oct2016 9:37 890.3 1
;
run;

data want (drop=I);
  array _date{1} _temporary_;
  array _tpmc{1} $8 _temporary_;

  set have (keep=date tpmc);

  /* preceding dates */
  time='00:00:00't;
  do I=date-5 to date-1; output; end;

  /* event date */
  set have;  output;

  /* trailing dates*/
  _date{1}=date; _tpmc{1}=tpmc;
  call missing (of _all_);
  tpmc=_tpmc{1};    time='00:00:00't;
  do date=_date{1}+1 to _date{1}+5; output; end;
run;

 

Frequent Contributor
Posts: 87

Re: how to repeat certain values while adding dates such as n days before and n days after?

I think I got it...

 

 

data DaysBeforeAfterEvent;
 set have;
 array x{1} _temporary_;
 x{1}=Date;
 call missing(pwc, pwsc, site, diam, pxmc, sf, et);
 time=0;
 do i=5 to 1 by -1;
   Date=x{1}-i;output;
 end;
 set have point=_n_; output;
 call missing(pwc, pwsc, site, diam, pxmc, sf, et);
 time=0;
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
run;

 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 254 views
  • 1 like
  • 2 in conversation