topic Re: Matching tables in SAS Procedures

Matching tables

robertrao — Thu, 05 Sep 2013 21:42:54 GMT

Hi Team,

I have table1 and table2

table1 has admission and discharge date info

table2 has each day census and the departments he shifts during the stay

we have to put the departments during admit-discharge period in a single row

i have below in table 1 the same patient having two different encounters
i want output like table3 and table4 also

Thanks

Re: Matching tables

Patrick — Thu, 05 Sep 2013 22:06:02 GMT

A SQL join of table 1 with table 2 using a join condition like

t1.id=t2.id and t1.admit<=t2.census<=t1.disch

should do the job for table 4.

Re: Matching tables

Haikuo — Fri, 06 Sep 2013 02:23:35 GMT

Ok, from first sight, it seems simple, but it is actually quite twisted. As Patrick pointed out, it probably easier to get Table4 first, from there, move on to Table3.

/*Table4: Option1- Hash(), not recommended, because I don't have a complete understanding upon Hash sorting, when key is not unique*/

data table4;

if _n_=1 then do;

if 0 then set table2;

declare hash h(dataset:'table2',multidata:'y');

h.definekey('id');

h.definedata(all:'y');

h.definedone();

end;

length unit $ 8;

set table1;

rc=h.find();

do while (rc=0);

if admit <= census <= disch then do;;

if unit ne department then do;

unit=department;

output;

end;

rc=h.find_next();

end;

rename census=date;

drop department encounter rc;

run;

/*Table4: Patrick's solution*/

proc sql;

create table table4_1 as

select t1.id, admit,disch, census as date, Department as unit from table1 t1, table2 t2

where t1.id=t2.id and t1.admit <= t2.census <= t1.disch

order by t1.id, admit, disch, date;

quit;

/*Note the notsorted option, which I think critical*/

data table4;

set table4_1;

by id admit disch unit notsorted;

if first.unit;

run;

/*Table3: transpose + merge*/

proc transpose data=table4 out=table3_1 prefix=date;

by id admit disch;

var date ;run;

proc transpose data=table4 out=table3_2 prefix=unit;

by id admit disch;

var unit ;run;

data table3;

merge table3_1(drop=_name_) table3_2(drop=_name_);

by id admit disch;

run;

I am sure there will be better solution emerging, as my head is not really functional after a long beat day, so this is just for you to start with. HTH.

Haikuo

Re: Matching tables

robertrao — Fri, 06 Sep 2013 14:44:18 GMT

Hi,

Thanks for the reply.

Could someone from the team translate these SQL to simple datasteps???

proc sql;

create table table4_1 as

select t1.id, admit,disch, census as date, Department as unit from table1 t1, table2 t2

where t1.id=t2.id and t1.admit <= t2.census <= t1.disch

order by t1.id, admit, disch, date;

quit;

Thanks

Re: Matching tables

Haikuo — Fri, 06 Sep 2013 14:57:09 GMT

This should do,

data table4_1;

if _n_=1 then do;

if 0 then set table2;

declare hash h(dataset:'table2',multidata:'y');

h.definekey('id');

h.definedata(all:'y');

h.definedone();

end;

length unit $ 8;

set table1;

rc=h.find();

do while (rc=0);

if admit <= census <= disch then output;

rc=h.find_next();

end;

rename census=date;

drop department encounter rc;

run;

Haikuo

Re: Matching tables

robertrao — Fri, 06 Sep 2013 15:05:31 GMT

Sir,

I appreciate youe esteemed help in this matter.

I do not also know nothing about Hash

I want it to be still simpler

Thanks

Re: Matching tables

Haikuo — Fri, 06 Sep 2013 16:03:12 GMT

To be honest with you, within the data step domain, I don't think you can get anything else as convenient as Hash() to mimic Proc SQL process. You could try using "set / point=" , however, from my past experience, the code will get even more complicated and you are most likely get a toll on efficiency because of the overhead.

Haikuo

Re: Matching tables

robertrao — Fri, 06 Sep 2013 17:07:14 GMT

hi,

Thanks a lot.

Could you please consider explaining to me what each of those statements in the hash method?

So that i will feel confident myself on what i have done

Thanks

Re: Matching tables

Haikuo — Fri, 06 Sep 2013 17:59:51 GMT

I will try to explain it in a nutshell, but your best bet is SAS doc:

data table4_1;

if _n_=1 then do;

if 0 then set table2; /*Prepare PDV for Hash*/

declare hash h(dataset:'table2',multidata:'y');/*Start defining Hash: Load table2 into Hash, key is not unique*/

h.definekey('id'); /*Hash key defined*/

h.definedata(all:'y');/*Hash data element will include all of the variables from source table*/

h.definedone(); /* Hash defining done*/

end;

length unit $ 8; /*Define new variable for downstream use*/

set table1;

rc=h.find(); /*Locate first key value in Hash that is same as the one from table1*/

do while (rc=0); /*Going through all of the obs that bears the same key in Hash*/

if admit <= census <= disch then output; /*output those obs that meet requirment*/

rc=h.find_next(); /*move to next obs with the same key*/

end;

rename census=date;

drop department encounter rc;

run;

Haikuo