BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
raqthesolid
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
  
 
Astounding
PROC Star

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

 

if first.id then event_all = .;

raqthesolid
Quartz | Level 8
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
Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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