Merge

[robertrao]

I have two tables as shown below. pr_id has orders placed as well as orders completed on a patient. Our aim is to get the orders completed on a patient

In the second int_table , or_id has the placed orders and int_id has completed orders.

i want to pull only the completed orders from proc table????

pat A represents same pateint but int_table has no patient info

proc table

pr_id    pat   descrip       date

101      A

25       A

int_table

or_id    int_id

101       25

[ballardw]

I think you need to show an example of what your output should look like.

[Ranny]

Karun,

I think you have to use conditional statement at some point with merge / proc SQL join but still if you will provide complete information then i can answer correctly.

[Astounding · Accepted solution]

This is a straightforward SORT and MERGE, as long as the order numbers are never re-used.  If the same order number could be used for more than one patient, there is not enough information in the data to solve the problem.

proc sort data=proc_table;

   by prd_id;

run;

proc sort data=int_table (keep=int_id) out=completed (rename=(int_id=pr_id));

   by pr_id;

run;

data completed;

   merge proc_table (in=in1) completed (in=in2);

   by pr_id;

   if in1 and in2;

run;

Good luck.

[robertrao]

WOWWWWWW

I think your method is pretty straightforward.

i was confused and used this lengthy confusing procedure.Could you also confirm if the method i was trying to use will also produce the same result????

I applied a CNTLIN format on proc table for pr_id after renaming pr_id to or_id

I then ued that format on int_table for or_id variable....    where put(or_id,format.)="true";

Then from the resulting dataset i wrote a format CNTLIN for  int_id and APPLIED IT BACK TO THE PROC TABLE....

set proc table

where put(pr_id,format.="true";

[ballardw]

Without seeing the code I can't say if your exact approach would work. You would need the where as a separate statement not a dataset option since you are using a function.

set proc_table;

where put(pr_id,format.)='true';

should work.

[Astounding]

It sounds like you jumped throuigh a few extra hoops.  The first half of your program would not be needed.  It would be enough to create the format based on the original INT_TABLE, then apply it to PROC_TABLE.  It would not impact the results if the format contains a few extra entries that are not found in PROC_TABLE.

The methods you used are a standard form of look-up in SAS.  Even if they would not be best for this particular problem (and that is a matter for debate), they are still tools that you should know.  So is SORT and MERGE.

To tell if the two methods produce identical results, you should test it.  That means (1) take the results of your CNTLIN approach, and sort it by PR_ID, then (2) run a PROC COMPARE to search for differences between the two output data sets.  Before doing that, check the logs from both approaches and see if the output data sets contain the same number of observations.

Good luck.

[robertrao]

Thanks a ton

Regards

[robertrao]

Hi,

If you dont mind i have a question????

to your post " It would not impact the results if the format contains a few extra entries that are not found in PROC_TABLE."

the reason i am using a format of the proc table because this  (contains my cohort for the period of interst).

i am worried if i dint do that......i would be picking 150k extra records(all the orders in the database ....ie which are not concerned with the patients i want........Do u think it is OK

[Astounding]

Sounds like it should be fine either way.

If PROC_TABLE contains just the cohort you want to examine, it wouldn't match any of the 150k extra records.  So the results should be the same.

[robertrao]

Great help.

Thnxxxx

[robertrao]

Hi ,

This code

proc sort data=int_table (keep=int_id) out=completed (rename=(int_id=pr_id));

   by pr_id;

run;

I guess we cant sort it by pr_id.......Is there any other way to rectify this error????

The only thing i think of is to use

by int_id

run;

will that solve the purpose????

Also these names cannot be the same right???

data completed;

   merge proc_table (in=in1) completed (in=in2);

   by pr_id;

   if in1 and in2;

run;

Regards