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

Hello,

 

I have a dataset with claims of medication, each line / observation is one claim. I have the patient_id and the date of the claim. Now I created a variable, which counts up the several claims per patient between the years 2014 and 2020, beginning with 1 for the first claim, 2 for the second, 3 for the third... The maximum number viaries from patient to patient.

 

Now I want to create a new variable, which counts the number of days between one claim to the previous within each patient, meaning the number of days between the second to the first, the third to the second...

 

I would be very happy for your help, thank you in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Example data helps. Such things as are your dates actually SAS date values (amazing how many people ask about manipulating "dates" that are actually character strings)? Is the data already sorted by patient_id and date?

 

Dummy code assuming the response to both questions above is yes:

data want;
   set have; 
   by patient_id;
   days = dif(datevariable);
   if first.patiend_id then days=.; /* or 0 if peferred*/
run;

When you use BY group processing in a data step SAS creates automatic variables that indicate whether a record is the first or last of a group, that are 1/0 for true/false, and referenced with the First. or Last. (pay attention to the dot in there).

The DIF function returns the difference of the current value of a variable with that of the previous record if used carefully. Since SAS date values are a number of days then DIF is the number of days between dates.

View solution in original post

4 REPLIES 4
ballardw
Super User

Example data helps. Such things as are your dates actually SAS date values (amazing how many people ask about manipulating "dates" that are actually character strings)? Is the data already sorted by patient_id and date?

 

Dummy code assuming the response to both questions above is yes:

data want;
   set have; 
   by patient_id;
   days = dif(datevariable);
   if first.patiend_id then days=.; /* or 0 if peferred*/
run;

When you use BY group processing in a data step SAS creates automatic variables that indicate whether a record is the first or last of a group, that are 1/0 for true/false, and referenced with the First. or Last. (pay attention to the dot in there).

The DIF function returns the difference of the current value of a variable with that of the previous record if used carefully. Since SAS date values are a number of days then DIF is the number of days between dates.

Tamino
Obsidian | Level 7
Thank you, that worked perfectly as I wanted.
Sorry about the dates, they were actually SAS date values.
mkeintz
PROC Star

Do you ever have multiple claims with the same date from a single patient? 

 

If so, what rule do you want to establish for the new variable?  I can imagine a couple possibilities:

  1. You would have some instances of days_between_events=0.  Or course this would be sensitive to reordering multiple claims for the same date.
  2. Each record on a given date has days_between_event set to the number of days since the last earlier event?
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tamino
Obsidian | Level 7
Thank you for your hint. I recognized that as I actually have some claims with the same date from a singe patient and the days_between_events=0 then. But as I want to exclude patients with less than 365d between their claims according to my research question, I ecluded them with a separate command (if claim_number>1 and days_between_events=0 then delete).

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 384 views
  • 2 likes
  • 3 in conversation