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
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
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.
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
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;
Thanks for your help too :smileygrin: Very helpful indeed In fact I used the index function!
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.