topic Re: Need to create a person level data set from a visit level data set (derived variables help) in SAS Programming

Need to create a person level data set from a visit level data set (derived variables help)

awardell — Sat, 02 Jan 2021 19:38:25 GMT

Hello,

I am working on a data set which was provided to me at a cycle level. This means that an individual with the same ID can have more that one visit. I need to create a person level data set in which there is only one occurrence of the participant's ID in the data set. I created derived variables at the cycle level, and now I need to create a person level counterpart.

For example:

I needed to create a yes/no variable determining if the patient was pregnant in any of the cycles. The person level counterpart would indicate whether the person was ever pregnant. How would you go about turning the yes/no variable into a person level variable?

I was thinking of doing something similar to the following, but it didn't work properly:

data want (drop = preg_counter); 
	set have; 
	by id cycle; 
  	retain preg_counter .;
  if first.id then preg_counter = .;
  if PREG = 1 then do;
    preg_counter = sum(preg_counter,1);
    PREG_COUNT = preg_counter;
  end;
  if PREG_COUNT>=1 then PREG_PERSONLEVEL=1;
run;

to achieve a data set like:

ID	CYCLE	Preg	Preg_PERSONLEVEL
1	1	0	1
1	2	1	1
1	3	0	1
2	1	0	0
2	2	0	0
3	1	1	1
4	1	1	2
4	2	0	2
4	3	1	2

Instead, this is produced, which is not helpful:

ID	CYCLE	Preg	Preg_PERSON LEVEL
1	1	0	.
1	2	1	1
1	3	0	.
2	1	0	.
2	2	0	.
3	1	1	1
4	1	1	1
4	2	0	.
4	3	1	2

After doing this, I would take the first ID for each patient to retain all of the relevant information in the person level data set. This solution may not be the best way to go about it, so any suggestions or help would be greatly appreciated.

Thank you in advance for all of your help! If there is a solution that avoids the use of SQL, that would be optimal because I am very new to SQL.

Datalines used are as follows:

data have; 
	input id cycle preg; 
	datalines; 
	1 1 0
	1 2 1
	1 3 0
	2 1 0 
	2 2 0
	3 1 1
	4 1 1
	4 2 0
	4 3 1
; 
run;

Re: Need to create a person level data set from a visit level data set (derived variables help)

Kurt_Bremser — Sat, 02 Jan 2021 19:50:10 GMT

Make use of the "auto-remerge" of SAS SQL:

proc sql;
create table want as
  select
    *,
    max(preg) as preg_person_level
  from have
  group by id
;
quit;

or use a data step with a double DO loop:

data want;
preg_person_level = 0;
do until (last.id);
  set have;
  by id;
  if preg then preg_person_level = 1;
end;
do until (last.id);
  set have;
  by id;
  output;
end;
run;

Re: Need to create a person level data set from a visit level data set (derived variables help)

awardell — Sat, 02 Jan 2021 20:14:32 GMT

Thank you so much! I didn't think about it that way!! I appreciate all of your help!

Re: Need to create a person level data set from a visit level data set (derived variables help)

tarheel13 — Sat, 02 Jan 2021 20:36:33 GMT

If you don't want to use do loops, you can just use set, by, retain.

proc sort data=have;
by id;
run;

data want;
set have;
by id;
retain preg_ever;
if first.id then do;
preg_ever=0;
end;
if preg=1 then preg_ever=1;
if last.id then output;
run;

proc print data=want;
run;

I think this gets your desired results.