Hi. I have data over time that contains the day someone initiated care (StartDate, DATE9.), how long they received care during that encounter (days_in_care, Num type), and the corresponding diagnosis for that visit (dx_1, Num type and the value is either 1 or 0 since only 1 diagnosis is tracked given the patient population). I've created two files ('same' contains everyone with the same diagnosis over time, 'not_same' contains everyone with diagnoses that change over time). I am interested in exploring patterns of how the diagnosis variable changes. Beyond the variables above, the ID is a character variable, and the data are arrayed in a long file--one row per encounter (the most encounters in the 'not_same' file for any individual is 40 and there are ~90 people in the dataset). I am struggling to implement code that:
Sample data:
ID (character) | StartDate (DATE9.) | dx_1 (numeric, 0/1) | days_in_care (numeric, ranges from 0, released in under 24 hours to 352) | want_lag_days | want_lag_encounters |
ff52fc4315ddb | 11Sep2016 | 0 | 15 | 16 | 2 |
ff52fc4315ddb | 18Nov2016 | 0 | 1 | 16 | 2 |
ff52fc4315ddb | 19Dec2016 | 1 | 217 | 16 | 2 |
ff52fc4315ddb | 16Aug2017 | 1 | 7 | 16 | 2 |
593f1e8e2615 | 16Jul2015 | 0 | 174 | 179 | 3 |
593f1e8e2615 | 9Jan2018 | 0 | 1 | 179 | 3 |
593f1e8e2615 | 9Nov2018 | 0 | 4 | 179 | 3 |
593f1e8e2615 | 8Apr2019 | 1 | 7 | 179 | 3 |
Have your tried transposing the data into a wide structured table as starting point? Based on sample data provided:
/* Sort data by id first, so by can be used in PROC Transpose */
PROC SORT DATA=WORK.BOOK1 OUT=SORT_BOOK1;
BY ID;
RUN;
/* Transpose data so date form the new columns, added a prefix of D_ to create valid column names */
PROC TRANSPOSE DATA=SORT_BOOK1
OUT=WORK.TRNS_BOOK1(DROP=_NAME_)
PREFIX=D_;
BY ID;
ID StartDate;
VAR dx_1;
RUN;
/* Use the Data Step to convert numeric columns to character and concatenate*/
DATA NEW;
SET WORK.TRNS_BOOK1;
ARRAY D{8} _NUMERIC_;
ARRAY CDATE{8} $;
DO I = 1 TO 8;
CDATE(I) = PUT(D(I),3.);
IF CDATE(I)=. THEN
CDATE(I)='';
END;
PATTERN = CATX(',', OF CDATE1-CDATE8);
KEEP CDATE1-CDATE8 PATTERN;
RUN;
/* Create list report */
PROC PRINT DATA=NEW;
RUN;
Result
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.