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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

 

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.

 

 


 

View solution in original post

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

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
Fluorite | Level 6
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
SAS Employee

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

Reeza
Super User

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
Fluorite | Level 6
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
Super User

 

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.

 

 


 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 4483 views
  • 0 likes
  • 4 in conversation