BookmarkSubscribeRSS Feed
robertrao
Quartz | Level 8

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

8 REPLIES 8
Patrick
Opal | Level 21

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.

Haikuo
Onyx | Level 15

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

robertrao
Quartz | Level 8

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

Haikuo
Onyx | Level 15

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

robertrao
Quartz | Level 8

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

Haikuo
Onyx | Level 15

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

robertrao
Quartz | Level 8

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

Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1026 views
  • 0 likes
  • 3 in conversation