topic Data Management: Longitudinal to cross sectional data in SAS Programming

Data Management: Longitudinal to cross sectional data

desireatem — Thu, 14 Apr 2022 15:50:33 GMT

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

Re: Data Management: Longitudinal to cross sectional data

maguiremq — Thu, 14 Apr 2022 16:38:21 GMT

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!

Re: Data Management: Longitudinal to cross sectional data

Ksharp — Fri, 15 Apr 2022 12:04:21 GMT

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;

Re: Data Management: Longitudinal to cross sectional data

maguiremq — Fri, 15 Apr 2022 14:58:57 GMT

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