Questions about Level 2 Practice: Subsetting by Multiple Conditions and Creating a Sorted SAS table

[easthill93]

Please help me with Lesson4 Reading and Filtering data_level 2 practice.

These are the following instructions.

1. Write a DATA step to read the np_species table and create a new table named fox as a permanent table in the EPG194/output folder.
2. Include only the rows where Category is Mammal and Common_Names includes Fox in any case.
3. Exclude the Category, Record_Status, Occurrence, and Nativeness columns from the output data.
4. Add the condition in the WHERE statement to exclude rows that include Squirrel.
5. Submit the program.

< My Answer>

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;

Q1> When I  run this code, the LOG says that "There were 0 observations read from the data set PG1.NP_Species". (Please refer to the box below). I wrote my code exactly the same as the answer provided. I don't know why there are 0 observations.

NOTE: There were 0 observations read from the data set PG1.NP_SPECIES.
       WHERE (Category='mammal') and UPCASE(Common_Names) like '%fox%' and UPCASE(Common_NAmes) not like '%squirrel%';
 NOTE: The data set WORK.FOX has 0 observations and 7 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.49 seconds
       cpu time            0.24 seconds

Q2> I'm confused when to use single quotes or double quotes around the text string when using the LIKE operator. In this question, the answer explained that I must use single quotes because % symbols enclosed in double quotes invoke macro language. I don't understand what this means.

Please answer these questions.

Thank you very much. 

[VDD]

did you assign the library PG1 when you started SAS?

you have to run the libname assignment or put it into you autoexec file each time you start SAS.

I think that was in the first assignments about setting up the library each time.

[easthill93]

Yes. I run the libname assignment so that I have PG1 file in My Libraries before I start to write code. 

[easthill93]

I solved it! 

It turned out that my WHERE statement was incorrect. 

<Previous Answer>

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;

<Revised Answer>

data fox;
	set pg1.np_species;
	where Category="Mammal" and Common_Names like '%Fox%' and 
    Common_Names not like '%Squirrel%';
	drop Category Record_Status Occurrence Nativeness;
run;

I changed the part that Common_Names include Fox in any case and exclude Squirrel in the WHERE statement.

Thank you very much for your help. 

[Astounding]

Here are some clues to get you moving.

When examining character strings, capitalization matters. These 3 strings are all different:

Mammal
MAMMAL
mammal

Similarly, these 3 are all different:

Fox
FOX
fox

On a related note, when you apply the UPCASE function to a character string, there is no way the result can contain a lowercase letter.

[easthill93]

I understood.

Thank you for your help.