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

Hello ,

I have a dataset of patient and the dr. office dates.

Some visits are mandatory while other are just when the patient has an issue.

The visits are grouped into periods.

Mandatory visits are 6 and visit 9

Have dataset

User               stdate                            enddate                   visit6dt                          visit9dt                              

A                2013-10-25                       2013-11-16                 2013-10-25                    2014-01-25

B                2013-02-01                      2013-04-21                 2013-03-10                    2013-06-10

C               2013-09-01                       2013-09                     2013-09-10                    2013-12-01

Want datset

User               stdate                            enddate                   visit6dt                          visit9dt                              

A                2013-10-25                       2013-10-25                 2013-10-25                    2014-01-25                            

A                2013-10-26                       2014-01-25                 2013-10-25                    2014-01-25

B                2013-02-01                       2013-02-01                2013-03-10                    2013-06-10

B                2013-02-02                       2013-06-10                2013-03-10                    2013-06-10

C               2013-09-01                       2013-09-09                 2013-09-10                    2013-12-01

C               2013-09-10                      2013-09-10                   2013-09-10                    2013-12-01

C               2013-09-11                      2013-09 -30                  2013-09-10                    2013-12-01

C               2013-10-01                      2013-12-01                 2013-09-10                    2013-12-01

becasue the en day in one case is missing i had to split it into 4 rows;

first until the 6 visit Then until the en of the month and then again until the end date.

Thank you for your help

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

Here is a solution for user C.

data have;

infile cards;

informat user $1. stdate enddate visit6dt visit9dt yymmdd10.;

format user $1. stdate enddate visit6dt visit9dt mmddyy10.;

input User$ stdate enddate visit6dt visit9dt;

cards;

A               2013-10-25                      2013-11-16                 2013-10-25                    2014-01-25

B               2013-02-01                      2013-04-21                 2013-03-10                    2013-06-10

C               2013-09-01                      2013-09                    2013-09-10                    2013-12-01

;

data prep;

set have;

output;

if missing(enddate) then do;

    enddate = visit6dt-1;output;

    enddate = visit6dt;output;

    enddate = intnx('month',stdate,0,'e');output;

    enddate = visit9dt;output;

end;

run;

data want;

set prep;

if missing(enddate) then delete;

run;

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

It would seem to be straightforward if then statements, something like:

     period=0; /* I.e. before v6 */

     new_start=stdate;

     new_end=visit6dt;

     output;

     period=6;

     new_start=visit6dt;

     new_end=visit9dt;

     output;

How you handle missing data items is up to you, maybe check length of date and assign a value or cover with some other error checking logic.

ballardw
Super User

I think you need to provide some more rules. Such as how the STDATE for the second or subsequent line is determined.

Why does User C end up with 4 lines?

And are any of these variables SAS date valued? Date manipulations are simpler with those.

Steelers_In_DC
Barite | Level 11

Here is a solution for user C.

data have;

infile cards;

informat user $1. stdate enddate visit6dt visit9dt yymmdd10.;

format user $1. stdate enddate visit6dt visit9dt mmddyy10.;

input User$ stdate enddate visit6dt visit9dt;

cards;

A               2013-10-25                      2013-11-16                 2013-10-25                    2014-01-25

B               2013-02-01                      2013-04-21                 2013-03-10                    2013-06-10

C               2013-09-01                      2013-09                    2013-09-10                    2013-12-01

;

data prep;

set have;

output;

if missing(enddate) then do;

    enddate = visit6dt-1;output;

    enddate = visit6dt;output;

    enddate = intnx('month',stdate,0,'e');output;

    enddate = visit9dt;output;

end;

run;

data want;

set prep;

if missing(enddate) then delete;

run;

Kc2
Quartz | Level 8 Kc2
Quartz | Level 8

Thank you everyone  for the quick response. Mark Johnson's solution is the closest one . I still need to make a few adjustemnt but I am getting there. (user C si the problem).

Steelers_In_DC
Barite | Level 11

I didn't see what you were doing with the duplicate A and B rows, here's another more complete solution:

data prep;

set have;

output;

if missing(enddate) then do;

    enddate = visit6dt-1;output;

    enddate = visit6dt;output;

    enddate = intnx('month',stdate,0,'e');output;

    enddate = visit9dt;output;

end;

if not missing(enddate) then do;

    retain User enddate visit6dt visit9dt;

    stdate = stdate+1;output;

end;

run;

data want;

set prep;

if missing(enddate) then delete;

run;

Kc2
Quartz | Level 8 Kc2
Quartz | Level 8

Thank you Mark.

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
  • 6 replies
  • 853 views
  • 0 likes
  • 4 in conversation