If/where contains problem while merging

[Way2go]

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

[Quentin]

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.

[Way2go]

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

[art297]

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;

[Way2go]

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

[mkeintz · Accepted solution]

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

[Way2go]

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