CODEWITHSUNDEEP
variablescomparesas
user877204
user877204

Reputation: 97

SAS - compare two variables in two datsets

I have two datasets of patients and am trying to make one data; my first data in inpatient admissions, and second is outpatient visits. I want to compare both data sets to see if outpatient visits data have a patient then I want to add a new variable to inpatient data let's say OP_var and label it as "Yes" and if an inpatient data patient is not present in outpatient data then i want to label OP_var as "No".

e.g.`

data inpatient;
input PID adm_dt dis_dt hsp_id @@;
cards;
1 01/01/2013 01/03/2013 10001
2 01/03/2013 01/04/2013 10110
3 01/10/2012 01/12/2012 10010
4 01/11/2013 01/17/2013 10000
;;

data outpatient;
input PID adm_dt_op dis_dt_op hsp_id_op @@;
cards;
1 01/05/2013 01/05/2013 10000
2 01/06/2013 01/06/2013 10111
4 01/19/2013 01/19/2013 10001
;;

data want;

PID  adm_dt     dis_dt     hsp_id adm_dt_op dis_dt_op   hs_id_op OP
1    01/01/2013 01/03/2013 10001  01/05/2013 01/05/2013 10000    Yes
2    01/03/2013 01/04/2013 10110  01/06/2013 01/06/2013 10111    Yes
3    01/10/2012 01/12/2012 10010  .          .           .       No
4    01/11/2013 01/17/2013 10000  01/19/2013 01/19/2013 10001    Yes`

Upvotes: 2

Views: 3666

Answers (2)

Joe
Joe

Reputation: 63424

Lots of solutions to this pretty basic problem. Merge the two datasets:

data want;
merge inpatient(in=i) outpatient(in=o keep=PID);
if i;
if o then Outpatient='Yes';
else outpatient='No';
run;

Works even if you have multiple variables to merge on, does require a sort so can be slow for very large datasets.

Create a format:

data op_format;
start=pid;
label='Yes';
fmtname='Outpf';
output;
if _n_ = 1 then do;  *add a row that instructs it what to do with nonmatching records;
hlo='o';
label='No';
output;
end;
run;

proc format cntlin=op_format;
quit;

data want;
set inpatient;
outpatient=put(pid,outpf.);
run;

Only works with one merge variable (without silly tricks like catting values together).

If these two don't work for you, there are other solutions - SQL, hash table, etc., depending on your data, your familiarity with various things, etc.

SQL solution, roughly:

proc sql;
create table want as
  select I.*, case when missing(O.PID) then 'No' else 'Yes' end as OP
  from inpatient I left join outpatient O
  on I.pid=O.pid;
quit;

Upvotes: 3

user2600629
user2600629

Reputation: 561

This should be helpful. Sorry I switched to R about a year ago and dont have SAS on my pc anymore to test.

    PROC SQL; 
     CREATE TABLE Patient_Matches AS 
     SELECT *
     FROM inpatient left join outpatient
     ON inpatient.PID = outpatient.PID 
     ; 
     QUIT;


     PROC SQL;
       ALTER TABLE Patient_Matches ADD OPchar(2);
       UPDATE Patient_MatchesSET myString='NO' where hs_id_op  = '';
       UPDATE Patient_MatchesSET myString='YES' where hs_id_op  <> '';
     quit;

Upvotes: 1

Related Questions