PLS EXPLAIN CODING---Level 2 Practice:Subsetting by Multiple Condition&Creating a Sorted Table

[melissagodfrey]

Can someone please help me understand this coding solutions in Lesson 4 preparing data 1st practice. 

The practice session requested

• Write a DATA step to read the pg1.np_species table and create a new table named fox. 
  Note: If you are using SAS Studio, try creating fox as a permanent table in the EPG194/output folder.
• Include only the rows where Category is Mammal and Common_Names includes Fox in any case. 
• Exclude the Category, Record_Status, Occurrence, and Nativeness columns. 

My attempt to code the answer was:

data out.fox ;
set PG1.NP_SPECIES;
keep Category= Mammal and Common_Names like ("fox" "Fox" "FOX";
drop Category Record_Status Occurrence Nativeness;
run;

solution:

data fox;
    set pg1.np_species;
    where Category='Mammal' and upcase(Common_Names) like '%FOX%';  
    drop Category Record_Status Occurrence Nativeness;
run;

I am confused by when where statement require ' ' or " " and when they do not. I am also confused by what the upcase means. Lastly I am confused why requesting the fox in any case, would = '%FOX%', as requesting the fox, in any case, has nothing to do with if there are letters before fox or after? Also I am confused by the next 2 steps. 

Notice that Fox Squirrels are included in the output table. Add a condition in the WHERE statement to exclude rows that include Squirrel. Submit the program and verify the results.

data fox;
    set pg1.np_species;
    where Category='Mammal' and upcase(Common_Names) like '%FOX%' 
        and upcase(Common_Names) not like '%SQUIRREL%';    
    drop Category Record_Status Occurrence Nativeness;
run;

My Attempt:

data out.fox;
set PG1.NP_SPECIES;
where Category='Mammal' and upcase(Common_Names) like '%FOX%' and Common_Names not like '%squirrel%';
drop Category Record_Status Occurrence Nativeness;
run;

Why do you have to state upcase (Common_Names) when typically you could just say varname not like 'squirrel'. Also why is squirrel written in capital letters, when it is not in capital letters in the table?

Last Step, Sort the fox table by Common_Names.

My attempt:

proc sort data=out.fox;
format Common_Names by fox;
run;

proc sort data=fox;
    by Common_Names;
run;

I dont understand how we all of a sudden have a fox table? when we only made an out.fox table above?

thanks for your time!

[PeterClemmensen]

Your question is three fold, so here are three answers:

1) In this case it does not matter whether you use double or single quotes. When you work with macro variables, always use double quotes.

2) The Upcase Function is used to consider the upcase version of the Common_Names variable to compare with something.

3) .. And that something is the string '%FOX%' where the percent sign is a wildcard operator representing any characters before or after the word 'fox'. 

[melissagodfrey]

thanks for the quick response. I have included other questions I have regarding the same practice Qs please review and respond when you have time.

[Panagiotis]

Hi @melissagodfrey, what other questions are you referring too? The sort ones?

[Reeza]

Please do not edit your question to add additional content. Instead either, post a new question if it's different than your original question or post your new comments/ questions as a response if it relates to your original question. 

This makes it really hard to understand a thread after the fact once the user is changing the original question. 

---

@melissagodfrey wrote:
thanks for the quick response. I have included other questions I have regarding the same practice Qs please review and respond when you have time.

---

[melissagodfrey]

hi there, thanks i certainly will in the future. actually panagiotis responded within 10 mins, i posted my question before i was done writing it and by the time i finished editting it the answer had already been written 🙂 i will certainly delete or rewrite in the future.

[Reeza · Accepted solution]

Why do you have to state upcase (Common_Names) when typically you could just say varname not like 'squirrel'. Also why is squirrel written in capital letters, when it is not in capital letters in the table?

Rather than check for all different case variations, you can make everything upper case and check it once instead of checking for Fox, FOX, fOX, foX, fox, etc. 

---

@melissagodfrey wrote:

Notice that Fox Squirrels are included in the output table. Add a condition in the WHERE statement to exclude rows that include Squirrel. Submit the program and verify the results.

data fox;
    set pg1.np_species;
    where Category='Mammal' and upcase(Common_Names) like '%FOX%' 
        and upcase(Common_Names) not like '%SQUIRREL%';    
    drop Category Record_Status Occurrence Nativeness;
run;

My Attempt:

data out.fox;
set PG1.NP_SPECIES;
where Category='Mammal' and upcase(Common_Names) like '%FOX%' and Common_Names not like '%squirrel%';
drop Category Record_Status Occurrence Nativeness;
run;

Last Step, Sort the fox table by Common_Names.

My attempt:

proc sort data=out.fox;
format Common_Names by fox;
run;

proc sort data=fox;
    by Common_Names;
run;

I dont understand how we all of a sudden have a fox table? when we only made an out.fox table above?

thanks for your time!

You created out.fox but if you check the code you were supposed to run, it's only a data set called fox, not OUT.FOX. 

It's possible there's a mistake in the course, but given what you've shown this seems to be the most likely issue.

---

[melissagodfrey]

very helpful thanks