topic Re: how to repeat certain values while adding dates such as n days before and n days after? in SAS Enterprise Guide

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

imanojkumar1 — Mon, 14 Nov 2016 14:21:50 GMT



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:

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 to repeat certain values while adding dates such as n days before and n days after?

mkeintz — Mon, 14 Nov 2016 14:48:22 GMT

Exactly what values are being repeated in your sample result?

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

imanojkumar1 — Mon, 14 Nov 2016 14:54:57 GMT

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).

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

mkeintz — Mon, 14 Nov 2016 15:26:11 GMT

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;

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

imanojkumar1 — Mon, 14 Nov 2016 15:11:38 GMT

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;