Hi Expert,
I asked this question yesterday and got the result.
I manipulated few data today but the output is not working correctly.
My criteria is to retrieve records from child table when country, product (using like operator logic) and dosage matches with same variable in table parent.
For combination of product, I don't want those records like 105
e.g. product mention in child table for record 105 is Gelusil where as the same product is available in parent table as in combination (Gelusil+Amlopidine).
How to ignore this type of records for this column or any alternative
data Child;
input pid country $ product & $25. dosage $;
cards;
101 France Atorvastatin Oral
102 France PaRacetamol .
103 Russia Nimusulide injection sc
104 China DIovan Tablet
105 Japan Gelusil Tablet
106 India Codeine+phosphate Tablet
;
run;
data Parent;
input country $ product & $25. dosage $;
cards;
JapaN Atorvastatin calcium Oral
China Atorvastatin Oral
France Atorvastatin calcium Oral
Russia Paracetamol Oral
India Paracetamol Oral
France Paracetamol Oral
RusSia Nimusulide sc
China diovan Syrup .
China diovan .
Japan Gelusil+Amlopidine Tablet
india Codeine+phosphate+salt Tablet
Japan Gelusil Tablet .
;
run;
proc sql;
create table match_country_prod_dos as
select A.*
from CHILD A
where exists(select distinct B.COUNTRY from PARENT B where (upcase(strip(B.COUNTRY))=upcase(strip(A.COUNTRY))
and index(upcase(strip(B.DOSAGE)), upcase(strip(A.DOSAGE))) >0 and index(upcase(strip(B.PRODUCT)),upcase(strip(A.PRODUCT)))>0));
quit;
Your program does not output line 105.
So confusing....
You state that you want to exclude 105, but you already are excluding it.
What do you want?
Please post the desired output.
If you run the complete program, I am getting three records like below.
How can I skip record number 105 as the child table contain product name as Gelusil where as parent contain combined product i.e. Gelusil+Amlopidine.
I want only output for record number 101 and 106 which is matching with parent table.
Any help would be highly appreciated
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.