BookmarkSubscribeRSS Feed
Sauda
Fluorite | Level 6

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;

 

 

5 REPLIES 5
ChrisNZ
Tourmaline | Level 20

Your program does not output line 105.

Sauda
Fluorite | Level 6
Yes, I don't want to display record when single product compare with combination. How can I exclude it. Can anyone help me
ChrisNZ
Tourmaline | Level 20

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.

 

Sauda
Fluorite | Level 6

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.

 

2017-11-17_14-36-54.jpg

Sauda
Fluorite | Level 6

Any help would be highly appreciated

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
  • 5 replies
  • 903 views
  • 0 likes
  • 2 in conversation