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
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.
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;
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
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
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
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
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
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.