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

Hi all,

I have a data set created with the following code:

data test;

input id $ dxday caetype $;

datalines;

01 1890 NONSP

01 2120 INFL

01 2128 INFL

01 2135 INFL

01 2137 NONSP

02 -775 INFL

02 -569 NONSP

03 3020 ARR

03 3026 NONSP

03 3027 NONSP

03 3175 INFL

;

run;

The 3 variables denote patient ID number, number of days the patient experienced a cardiac adverse event (CAE) relative to a certain vaccination, and type of CAE. I'm trying to calculate differences in the number of days from a baseline value for each patient, where the baseline value is the first occurrence of a CAE with type 'INFL'. So, for ID 01, the first INFL CAE occurs at the 2nd observation (dxday=2120); for ID 02, the first INFL CAE occurs at the 1st observation (dxday=-775); and for ID 03 the first INFL CAE occurs at the last observation (dxday=3175).

What I'd like to do is to create a difference variable (DIFF) that calculates time differences based on the correctly identified baseline time value for each patient. So, what I'd like to end up with is a data set that looks like this:

iddxdaycaetypeDIFF
011890NONSP-230
012120INFL0
012128INFL8
012135INFL15
012137NONSP17
02-775INFL0
02-569NONSP206
033020ARR-155
033026NONSP-149
033027NONSP-148
033175INFL0

Can anyone provide any insight on how to do this? Thanks so much!

chucsp

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Chucs,

SQL is more of my weak spot, so I would go about it like this.

data want;

  do until (last.id);

     set have;

     by id;

     if baseline =. and caetype='INFL' then baseline = dxday;

  end;

  do until (last.id);

     set have;

     by id;

     diff = dxday - baseline;

     output;

  end;

run;

The logic assumes that your data are already in order BY ID DXDAY.  Optionally, you might want to drop BASELINE;

Good luck.

View solution in original post

4 REPLIES 4
Anotherdream
Quartz | Level 8

First get a dataset that keeps the first value of caetype per each ID, along with the dxday, then re-merge that dataset onto the original and find the difference. The following code will do the trick rather easily.

Note: You did not specify how to define the "First" value of each INFL per id, so I am going to assume the base order of the data (aka the variable _n_) is the correct variable to find the "first" value.

data test1;

set test;

ordervar=_N_;

if caetype='INFL';

run;

sort data=test1;

by id ordervar;

run;

data joinvalues(drop=ordervar caetype);

set test1;

by id ordervar;

if first.id=1 then output;

run;

proc sql;

create table answer as

select t1.*

,t1.dxday-jv.dxday as diff

from test t1

join joinvalues jv on jv.id=t1.id;

quit;

run;

sam369
Obsidian | Level 7

Hi Chucs,

May be some one come up with better answer. here is the sample code where you can get the desired o/p

proc sql;

create table want as

select a.*,b.baseday,(dxday-baseday) as diff

from have as a left join(select id,dxday as baseday from have(where=(caetype eq 'INFL'))

                          group by id having dxday=min(dxday)) as b

on a.id=b.id

order by a.id,a.dxday;

quit;

Thanks

Sam

Astounding
PROC Star

Chucs,

SQL is more of my weak spot, so I would go about it like this.

data want;

  do until (last.id);

     set have;

     by id;

     if baseline =. and caetype='INFL' then baseline = dxday;

  end;

  do until (last.id);

     set have;

     by id;

     diff = dxday - baseline;

     output;

  end;

run;

The logic assumes that your data are already in order BY ID DXDAY.  Optionally, you might want to drop BASELINE;

Good luck.

chucs
Calcite | Level 5

Thanks to everyone for their response! I'll give them a try.

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
  • 4 replies
  • 3973 views
  • 11 likes
  • 4 in conversation