topic Re: If/where contains problem while merging in SAS Procedures

If/where contains problem while merging

Way2go — Mon, 06 Aug 2012 13:09:05 GMT

Hello! I have to perform a task in SAS but I don't know how to do 😕 I've already searched in the forums but I couldn't find anything. I hope you can help me

Let's say you are merging 2 data set and some of the variables are: ITEM_description (string), producer (string) and brand (string) but the last 2 have missing values. Your goal is to put a value (string) to producer and brand that depends on ITEM_description. I'll make an example:

pdv - ITEM_desc - producer - brand

12 - Nutella 500g - (missing) - (missing)

76 - Nutella 750g - (missing) - (missing)

data C;

merge A B;

by pdv;

where ITEM_desc CONTAINS Nutella then do;

producer=Ferrero;

brand=Nutella;

end;

run;

So I know that WHERE doesn't work with THEN and DO but IF doesn't work with CONTAINS (that is the operator I need). How should I write the syntax to make it work? Thank you in advance

Re: If/where contains problem while merging

Quentin — Mon, 06 Aug 2012 13:25:15 GMT

Hi,

Suggest you look into the character functions available in SAS.

I suspect:

if findw(ITEM_desc,"Nutella") then do ;

could give you what you want.

Hopefully this is a one-to-one merge. If not, you can run into surprises because PRODUCER and BRAND will be retained across iterations of the datastep.

HTH,

--Q.

Re: If/where contains problem while merging

art297 — Mon, 06 Aug 2012 13:31:25 GMT

Alternatively, if the string you are searching for might contain additional characters before or after it (i.e., it isn't necessarily a word), you could use the index function. e.g.:

data a;

informat item_desc $30.;

input pdv ITEM_desc (producer brand) ($);

cards;

12 Nutella 500g .

76 Nutella 750g .

;

data b;

input pdv price;

cards;

12 2.5

76 3.0

;

data C;

merge A B;

by pdv;

if index(ITEM_desc,'Nutella') then do;

producer='Ferrero';

brand='Nutella';

end;

run;

Re: If/where contains problem while merging

mkeintz — Mon, 06 Aug 2012 13:35:09 GMT

Waytogo:

You are merging data sets A and B by pdv, which means that for a given PDV, the value ot item_desc, producer, and brand in B will overwrite the values in A, even when the B values are missing. Is that what you want?

As to your question, you could replace the "where ... contains" expression with

if index(item_desc,'Nutella') > 0 then do; ... ... end;

The index function returns the leftmost position in ITEM_DESC of the text "Nutella". If "Nutella" is not in item_desc, then the index functions yields a zero. BTW, can you be certain the text your are looking for will have precisely the mixed cases "Nutella"? If not then you might want to modify to
if index(upcase(item_desc,'NUTELLA')>0 then do; ... ... end;

I take it that you accidentally forgot to quote "Nutella", because left unquoted, SAS looks for a variable named nutella rather than the literall text.

Also, here's the SAS 9.2 web page of functions, orgainized by category: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245852.htm

Re: If/where contains problem while merging

Way2go — Mon, 06 Aug 2012 22:24:25 GMT

Actually is a 3 dataset merge where item_descr producer and brand appear in different datasets, so there isn't the overwriting issue! (I said that i only had 2 datasets A and B just to simplify the question)

Anyway I have tried this if index(item_desc,'Nutella') > 0 then do; ... ... end; and it works! Also you supposed right: the text in item_descr is all in uppercase but I didn't need to use this if index(upcase(item_desc,'NUTELLA')>0 then do; ... ... end; because it worked in the first way.

Thank you for your help

Re: If/where contains problem while merging

Way2go — Mon, 06 Aug 2012 22:27:37 GMT

As I said to mkeintz this is a 3 dataset merge

Btw I haven't tried with your method yet, but sure thing is I'll give it a try anyway

Thanks for your help

Re: If/where contains problem while merging

Way2go — Mon, 06 Aug 2012 22:31:19 GMT

Thanks for your help too :smileygrin: Very helpful indeed In fact I used the index function!