SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Ripple of a date

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Ripple of a date

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

 


Accepted Solutions
Solution
‎11-06-2017 11:18 PM
Super User
Super User
Posts: 8,152

Re: Ripple of a date

Posted in reply to raqthesolid

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;
  
 

View solution in original post


All Replies
Super User
Posts: 20,203

Re: Ripple of a date

Posted in reply to raqthesolid

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

Solution
‎11-06-2017 11:18 PM
Super User
Super User
Posts: 8,152

Re: Ripple of a date

Posted in reply to raqthesolid

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;
  
 
Super User
Posts: 5,598

Re: Ripple of a date

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

 

if first.id then event_all = .;

Contributor
Posts: 69

Re: Ripple of a date

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
Respected Advisor
Posts: 4,185

Re: Ripple of a date

Posted in reply to raqthesolid

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

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 155 views
  • 0 likes
  • 5 in conversation