topic Re: Ripple of a date in SAS Data Management

Ripple of a date

raqthesolid — Mon, 06 Nov 2017 16:21:13 GMT

Hello experts,

I have three variables in my data set,

1) Date

2) Event date (this is present for few days not all of the data) this variable is missing for majority of data

3) firm ID

What i Want:
I want to create a new variable "event all" which should have same event date in the previous date.

for example, my data start from 1st January 2017. First event occurs on 25 Feb 2017. I want to give "25 Feb 2017' date to all the observation from 1st January 2017 to 25 Feb 2017 in the new variable "event all".

In my data, there are multiple event and multiple firms, so i want to deal each firm and its date separately.

kindly help me to program this.

Regards

Data have;

Firm date event_date event_all

1 20170101 - 20170401

1 20170201 - 20170401

1 20170301 - 20170401

1 20170401 20170401 20170401

2 20160101 20160301

2 20160201 20160301

2 20160301 20160301 20160301

2 20160401 - -

My data has more than 400 firms and about 20 year of data.
any help will be appreciated.

Thanks in advance

Re: Ripple of a date

Reeza — Mon, 06 Nov 2017 16:24:33 GMT

Please post your data as a data step and show what you've tried so far.

I'm not seeing how your title is applicable to your question either...

Re: Ripple of a date

RW9 — Mon, 06 Nov 2017 16:44:46 GMT

Your problem seems to be a simple one. The main thing is to reverse sort the data - then simple retain, then sort back again.

proc sort data=have out=want;
  by id descending date;
run;
data want;
  set want;
  by id;
  retain event_all;
  if event_date ne . then event_all=event_dt;
run;
proc sort data=want;
  by id date;
run;

Re: Ripple of a date

Astounding — Mon, 06 Nov 2017 17:44:03 GMT

@RW9 has a viable approach here. You might want to add (immediately following the RETAIN statement):

if first.id then event_all = .;

Re: Ripple of a date

raqthesolid — Tue, 07 Nov 2017 03:02:39 GMT

Thank you for your reply. I think i could not through my idea well. I donot have this Event_all column in my data. I want to create this using other three columns.
Simple i want to fill the missing values in Event data with the next non-missing value for each firm separately.
good day

Re: Ripple of a date

Patrick — Tue, 07 Nov 2017 03:59:24 GMT

@raqthesolid

I believe the people having responded to you understood what you've been asking for. Did you try to understand their answers/code provided as it looks to me that's doing what you're after.