topic Re: How to Merging one to many but only retaining the third observation of the second file in New SAS User

How to Merging one to many but only retaining the third observation of the second file

rla_hoz — Fri, 13 Dec 2019 19:58:26 GMT

Dear SAS community,

I have two SAS files (Lung) that has one row per study subject. This first file has the baseline characteristics. The second SAS file (Lung_followup) has the follow-up data for the study subjects. This second file may have zero or more rows per study subject. The number of rows is dependent on the number of years of follow-up the study subject has accrued. For example, those that do not have one year of follow-up do not have any rows in this second file while those that have 5 years of follow-up will have 5 rows. The second file has the rows in chronological order, e.g. the first row corresponds to the first year follow-up and the second to the second year follow-up.

I am trying to merge the two aforementioned SAS files in a single file (Lung_merge). My goal is to have a file that has one row per patient and contains the baseline characteristics of the first SAS file (Lung) and the 3 year follow-up data (third row of the second file). If the study subject doesn't have 3 year follow-up data I don't want to include it in the merged file (Lung_merge).

Could you please help me with the code? This is what I have so far and I am quite far from achieving my goal.

Data Lung_Merge;

Merge Lung (IN=in1) Lung_followup (IN=in2);

By TRR_ID_CODE;

Run;

Thank you for your advice. I use SAS 9.4

Best

Re: How to Merging one to many but only retaining the third observation of the second file

Kurt_Bremser — Fri, 13 Dec 2019 20:03:30 GMT

Data Lung_Merge;
Merge
  Lung (IN=in1)
  Lung_followup (IN=in2)
;
By TRR_ID_CODE;
if first.trr_id_code
then _count = 1;
else _count + 1;
if in1 and count = 3;
Run;

Re: How to Merging one to many but only retaining the third observation of the second file

jvdl — Fri, 13 Dec 2019 20:08:49 GMT

It would help if you could show a data snippet, or at least provide some dummy data in a cards/datalines statement.

You've mentioned that for "Lung_followup" the row number for each subject corresponds to the follow-up year. Are you sure you can rely on this property to always be true?

In that case, you can probably count the row number per subject, and just keep where this counter equals 3. This code may need a bit of tweaking, I just did it off the top of my head.

proc sort data=lung_followup out=lf_sorted;
	by subjid;
run;

data lf_with_counter;
	set lf_sorted;
	by subjid;

	if first.subjid then counter = 1;
	else counter + 1;
run;

data Lung_Merge;
	merge Lung (IN=in1) lf_with_counter (IN=in2 where=(counter = 3));
	by subjid;

	if in1 and in2;
run;