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
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.
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.