DATA Step, Macro, Functions and more

Merge

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,041
Accepted Solution

Merge

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


Accepted Solutions
Solution
‎12-06-2012 12:30 PM
Super User
Posts: 5,516

Re: Merge

Posted in reply to robertrao

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.

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Merge

Posted in reply to robertrao

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

Occasional Contributor
Posts: 19

Re: Merge

Posted in reply to robertrao

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.

Solution
‎12-06-2012 12:30 PM
Super User
Posts: 5,516

Re: Merge

Posted in reply to robertrao

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.

Super Contributor
Posts: 1,041

Re: Merge

Posted in reply to Astounding

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";

Super User
Posts: 11,343

Re: Merge

Posted in reply to robertrao

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.


Super User
Posts: 5,516

Re: Merge

Posted in reply to robertrao

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.

Super Contributor
Posts: 1,041

Re: Merge

Posted in reply to Astounding

Thanks a ton

Regards

Super Contributor
Posts: 1,041

Re: Merge

Posted in reply to Astounding

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

Super User
Posts: 5,516

Re: Merge

Posted in reply to robertrao

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.

Super Contributor
Posts: 1,041

Re: Merge

Posted in reply to Astounding

Great help.

Thnxxxx

Super Contributor
Posts: 1,041

Re: Merge

Posted in reply to Astounding

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 352 views
  • 3 likes
  • 4 in conversation