Dear Sir/Madam,
I have this longitudinal that, I want to reduce to cross-sectional data.
If Lmin and Rmin are present together with ICU LOS Age we keep this row and delete all other rows for this id.
If Lmin and Rmin are present but not any of ICU LOS Age , we keep the values, that is the first time they are recorded.
If Lmin and Rmin are not recorded but ICU LOS Age are recorded, we keep the values recorded to fit where missing.
If Lmin and Rmin are not recorded together with ICU LOS Age we delete (he data has other additional variables.
To summarize, for each person, use the first recorded value of Lmin, Rmin, ICU, LOS and age and drop the rest.
Below is an example of the longitudinal data and newly created cross sectional data.
id Lmin Rmin ICU LOS Age
1 2 5 19
1 1 2
1 1
2 1 5
2 2 5 6 27
2 1 2
2 2
New Data to be created
Id Lmin Rmin ICU LOS Age
1 1 2 2 5 19
2 1 2 5 6 27
data have;
infile datalines delimiter = ',' dsd missover;
input id Lmin Rmin ICU LOS Age;
datalines;
1,,,2,5,19
1,1,2,,,,
1,,1,,,,
2,1,,5,,
2,,2,5,6,27
2,1,2,,,,
2,,2,,,,
;
run;
data temp;
set have;
n+1;
run;
proc sort data=temp out=temp1;
by id descending n;
run;
data want(drop=n);
update temp1(obs=0) temp1;
by id;
run;
data have;
infile datalines delimiter = ',' dsd missover;
input id Lmin Rmin ICU LOS Age;
datalines;
1,,,2,5,19
1,1,2,,,,
1,,1,,,,
2,1,,,5,,
2,,2,5,6,27
2,1,2,,,,
2,,2,,,,
;
run;
proc means data = have noprint;
class id; /* Produce by ID row */
ways 1; /* Do it for the individual pairs */
var lmin rmin icu los age; /* variables to be maxed */
output out = want (drop = _:) /* create want data set, drop automatic variables */
max = / autoname; /* get the max of all vars in `var` statement, name them with _max at the end */
run;
In the future, please give us example data as I did in the first DATALINES statement. It helps us solve these issues faster. I had to go through and figure out which number aligned with each column. Thanks!
data have;
infile datalines delimiter = ',' dsd missover;
input id Lmin Rmin ICU LOS Age;
datalines;
1,,,2,5,19
1,1,2,,,,
1,,1,,,,
2,1,,5,,
2,,2,5,6,27
2,1,2,,,,
2,,2,,,,
;
run;
data temp;
set have;
n+1;
run;
proc sort data=temp out=temp1;
by id descending n;
run;
data want(drop=n);
update temp1(obs=0) temp1;
by id;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.