BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Way2go
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

6 REPLIES 6
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Way2go
Calcite | Level 5

As I said to mkeintz this is a 3 dataset merge Smiley Wink

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

Thanks for your help Smiley Happy

art297
Opal | Level 21

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
Calcite | Level 5

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

mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Way2go
Calcite | Level 5

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 Smiley Wink

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!

What is Bayesian Analysis?

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.

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
  • 6 replies
  • 943 views
  • 8 likes
  • 4 in conversation