BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

11 REPLIES 11
ballardw
Super User

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

Ranny
Obsidian | Level 7

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
PROC Star

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
Quartz | Level 8

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
Super User

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
PROC Star

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
Quartz | Level 8

Thanks a ton

Regards

robertrao
Quartz | Level 8

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
PROC Star

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
Quartz | Level 8

Great help.

Thnxxxx

robertrao
Quartz | Level 8

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 11 replies
  • 1276 views
  • 3 likes
  • 4 in conversation