Data Management: Longitudinal to cross sectional data

[desireatem]

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    

[maguiremq]

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!

[Ksharp · Accepted solution]

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;

[maguiremq]

Ah, just read the 'first' requirement. Good catch.