dataset merge

[velango]

need to combine 2 datasets ANC & OTHER 

pull patient ids  who received both anc (supplies) and other (drug) in above 2 datasets

id	category	category desc
A	11	DRUG
A	11	DRUG
A	13	ANC
A	13	ANC
B	11	DRUG
C	11	DRUG
D	11	DRUG
F	11	DRUG
G	11	DRUG
G	13	ANC
G	13	ANC
G	13	ANC
G	13	ANC
G	13	ANC
H	11

i want id A& G  show up in the report

i used below code

data PTNT_both1 (keep= id categorycategory_desc) ;
merge ptnt_anc_out (in=anc) ptnt_other_out (in=other);
by patient_id;
if anc=1 and other=1;
run;

 not getting all records from A & G

 Please help

[Kurt_Bremser]

You talk about two datasets, but I only see a screen-copy of one. Please provide both datasets, and what you expect as a result out of them.

[velango]

here are 2 datasets attached. i want to get patient id  with both category _id 11 & 13  with all records

[Kurt_Bremser]

These are not SAS datasets, these are Excel spreadsheets. Such files do not provide sufficient information about your variables.

This is the proper way to present example data, so that everyone can reliably work with it:

data ptnt_anc;
infile datalines dlm="|";
input PATIENT_ID :$5. ITEM_NAME :$30. DRUG_CATEGORY :$5. ITEM_CATEGORY_ID;
datalines;
194025|ALCOHOL PREP PADS (LRG)|ANC|13
194025|ALCOHOL ++ PREP PAD ST LRG|ANC|13
194025|UNDERPAD DURASOR 23"X36"|ANC|13
194025|SYRINGE 50-60ML L/L NO NEEDLE|ANC|13
317798|ALTERA HANDSET (NO COST)|OTH|13
329673|ALTERA HANDSET (NO COST)|OTH|13
;

data ptnt_other;
infile datalines dlm="|";
input PATIENT_ID :$5. ITEM_NAME :$30. DRUG_CATEGORY :$5. ITEM_CATEGORY_ID;
datalines;
194025|CUVITRU 20%|IGIV|11
194025|CUVITRU 20%|IGIV|11
303194|KISQALI B/P (TD 600MG/DY)|ONCOL|11
317798|CAYSTON INH SOLN|CF|11
317798|PULMOZYME INH SOLN|CF|11
329673|CAYSTON INH SOLN|CF|11
331633|PULMOZYME INH SOLN|CF|11
339601|TADALAFIL|PAH|11
;

Please run these codes and see if the resulting datasets match yours in terms of structure. Then provide the expected output.

[Peter_L]

Use SQL. It is far easier than the data step for such things. Use SELECT FROM and JOIN ON to do the work of MERGE if you want side-by-side columns or to select rows in one table based on rows in another. If you want stacked columns you might need also to use SELECT UNION later. I can't comment more as the question is not well enough defined (see other replies).

[velango]

thank you. i tried SQL outer join . yes it is side by side

 i want them stacked. i will try select union  

thanks for your help