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

Hello,

 

I want to get each date with non-missing dates.

 

My approach is if I can replace missing dates to the previous no-missing value,  However, I'm having difficulty to replace these missing value.,

 

I appreciate any help.

 

 

data test;

input subid$ startdate enddate;

datalines;

XYZ-123-001-001  2013-04-04 2013-04-05

XYZ-123-001-001  2013-05-23 .

XYZ-123-001-001  2013-06-07 2013-06-13

XYZ-123-001-001  2013-06-09 2013-06-15

XYZ-123-001-001  2013-07-07 .

XYZ-123-001-001  2013-07-27 .

XYZ-123-001-001  2013-08-07 2013-08-13

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Are you looking for something like this?

data have;
input subid$ startdate enddate;
informat startdate enddate yymmdd10.;
format startdate enddate yymmddd10.;
datalines;
XYZ-123-001-001  2013-04-04 2013-04-05
XYZ-123-001-001  2013-05-23 .
XYZ-123-001-001  2013-06-07 2013-06-13
XYZ-123-001-001  2013-06-09 2013-06-15
XYZ-123-001-001  2013-07-07 .
XYZ-123-001-001  2013-07-27 .
XYZ-123-001-001  2013-08-07 2013-08-13
;
run;

data want;
set have;
retain keepdate;
if enddate ne .
then keepdate = enddate;
else enddate = keepdate;
drop keepdate;
run;

But it will create enddates that make no sense, so could you be a little more specific about the rules, or provide an example "want" dataset?

View solution in original post

8 REPLIES 8
Kurt_Bremser
Super User

Are you looking for something like this?

data have;
input subid$ startdate enddate;
informat startdate enddate yymmdd10.;
format startdate enddate yymmddd10.;
datalines;
XYZ-123-001-001  2013-04-04 2013-04-05
XYZ-123-001-001  2013-05-23 .
XYZ-123-001-001  2013-06-07 2013-06-13
XYZ-123-001-001  2013-06-09 2013-06-15
XYZ-123-001-001  2013-07-07 .
XYZ-123-001-001  2013-07-27 .
XYZ-123-001-001  2013-08-07 2013-08-13
;
run;

data want;
set have;
retain keepdate;
if enddate ne .
then keepdate = enddate;
else enddate = keepdate;
drop keepdate;
run;

But it will create enddates that make no sense, so could you be a little more specific about the rules, or provide an example "want" dataset?

gowtham112
Obsidian | Level 7

Thank you Kurtbremser ,for your valuable suggestion  actually i want to create TEAE flag inorder to do that i want end dates so  in my dataset some of end dates are missing so i want that missing dates to create..

Reeza
Super User

Your end date is before your start date. I don't think that makes sense. 

Why are the values missing?

gowtham112
Obsidian | Level 7
Thank you Reeza for your valuable suggestion actually in my data no end date was before start date.Actually i want to create TEAE flag to do so i want end
dates.. so i want to replace those missing values to create TEAE...
ballardw
Super User

Something else to consider: What if the first record for a subject is missing the enddate? Your basic rule would be using the enddate from another subject.

 

You might consider adding a fixed number of days or advancing to end of month or similar rule for missing enddates.

 

And do you have any missing start dates?

gowtham112
Obsidian | Level 7
Thank you Ballardw for your suggestion, In my data there were no missing start dates ,Can you suggest me how to replace end date with end of month.
ballardw
Super User

Not the only way but

 

if missing enddate then enddate= intnx('month',startdate,1)-1;

 

Will give the end day of the month assuming the date variables are actually SAS date values.

This works by the default advance a date by one month gets to the first day of the following month. Subtracting one gets one day before.

 

Or

enddate= intnx('month', startdate,0,'e');   uses the alignment 'e', for end of month, and advances 0 months(i.e. the same month).

Reeza
Super User

@gowtham112 FYI - You don't need to reply to each post individually. It makes the thread very hard to read.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1771 views
  • 1 like
  • 4 in conversation