Merging two time-varying files with different variables (exposure and outcome)

[mmovahed]

Hi everyone

I like to create dataset C from two dataset A (time varying exposure) and B (time-varying outcome) (please see attached). 

Question: Which SAS codes (program) can I use to have dataset C from two dataset A and B

[SASJedi · Accepted solution]

Something like this, maybe?

data a;
	infile datalines dsd;
	INPUT PatID Days Treatment;
	label patid='Patient ID' Days= 'Days from diagnosis' ;
datalines;
1,0,0
1,100,1
1,150,1
1,200,1
1,250,0
;

title "A";
proc print; run;

data b;
	infile datalines dsd;
	INPUT PatID Days Hospitalized;
	label patid='Patient ID' Days= 'Days from diagnosis' ;
datalines;
1,0,0
1,100,0
1,180,1
1,220,1
;

title "B";
proc print; run;

data C;
	merge a b;
	by PatID days;
        /* If new Hospitalized between Treatment dates, use previous treatment */
	Treatment=coalesce(Treatment,lag1(Treatment));
        /* If new Treatment between Hospitalized dates, use 0 */
	Hospitalized=coalesce(Hospitalized,0);
run;

title "C";
proc print; run;

[mmovahed]

Thanks Jedi SAS

It seems working. Now I have all actual values for treatment and hospitalization after merging.

Regards

[ballardw]

The basic part of your question looks like this should be part of the solution:

data c;
   set a
         b
  ;
run;

proc sort data=c;
by patid daysfromdiagnosis;
run;

Some items to address:

For future questions if you have SAS data sets use the names of the variables. "Days from diagnosis" by default is not going to be a valid variable name.

Second: are you sure that you want values that are not in the original data to be 0 or 1 and not missing? We have no context for what the values of Treatment or Hospitalized actually mean in real world terms and I do not like "guessing" what the underlying rules for assigning such values might be.

There could very well be another step after the Proc Sort of the combined data sets.

Note: Merge in SAS terms means a combination from side by side and has some restrictions for most usage, such as sorted by one or more common variables. We do not know that is the real case.

Please provide data in the form of working data sets so we at least know variable types and actual names. If you can't do that provide text pasted into a text box opened with the </> icon above the main message window. We can't code against values buried in pictures.

[mmovahed]

Thanks for your reply.

I have already tried this code. the problem is your second concern about the real value for treatment after merging. I know the approach in the STATA ( tvc_merge) to deal with this actual values . but I am looking for an appropriate code in SAS in this regard.