topic Re: dataset merge in SAS Enterprise Guide

dataset merge

velango — Tue, 15 Mar 2022 13:31:57 GMT

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

Re: dataset merge

Kurt_Bremser — Tue, 15 Mar 2022 13:36:26 GMT

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.

Re: dataset merge

velango — Tue, 15 Mar 2022 14:29:21 GMT

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

Re: dataset merge

Kurt_Bremser — Tue, 15 Mar 2022 15:35:13 GMT

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.

Re: dataset merge

Peter_L — Tue, 15 Mar 2022 16:18:34 GMT

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).

Re: dataset merge

velango — Tue, 15 Mar 2022 18:04:07 GMT

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