Please post the complete log. Use the {i} button for logs, and the "little running man" for code.
Hi @Hang
Welcome to the community!
What is the type of column "SIC": character or numeric?
If it is numeric, you need to remove quotation marks: Where SIC = 6000
In your code, you have some missing semi-colons and some misspelling in the dataset name. Here is the correction:
libname mydata ‘directory path’;
data work.df;
set mydata.nameoffile;
run;
proc sql;
select columns from mydata.nameoffiles where SIC = ‘6000’;
quit;
Hi, thank you for your reply
PLEASE(!!!) post the whole(!) log, and USE THE {i} BUTTON for this. DO NOT SKIP THIS.
It seems that SIC is of type character, and you need to use the quotes. Please change the code, run it, and post the complete log as requested.
full log
So it's a clear case for Maxim 3: Know Your Data.
First, run a proc contents on MYDATA.UK to see the length and format of SIC (if a format is assigned).
Then, create a quick sample:
data test;
set mydata.uk (keep=sic obs=100);
checkvar = put(sic,$HEXxx.);
run;
where "xx" is double the defined length of SIC. You can then inspect the hex values to see if you have special characters, leading blanks, etc.
The proc contents is not meant to immediately solve the problem, but its output allows you to set up further steps to get to know your data.
What does the output of proc contents say about column SIC?
@Kurt_Bremserit says that the variable SIC is character
yeah I figured proc contents is not the solution. This is the first time I used this so I'm not sure what to do exactly. so after going youtube and taking in you guys' suggestions
this is what I've got so far:
CODE
You have to look at the OUTPUT, not the log from proc contents. It will tell you the defined length of SIC, and if a format (other than just $w.) is assigned to it.
Then you can run the other step I showed you, that helps you in getting a grasp on the REAL values of SIC.
And while you do this, follow my Maxim #1 and read up on the documentation of all these tools we show you, to familiarize yourself with them and how they have to be used.
Very likely, the data does not behave as expected. You have no "6000" values in your data set. Here's a way to check, while examining your data a bit:
proc freq data=mydata.uk;
tables SIC;
where SIC =: '60';
run;
Remember to include the colon in the =: comparison.
Edited: Note the change, using "60" instead of "6000".
@AstoundingI've done that and the results still came out blank, does it mean that I have nothing satisfy the condition?
Yes. There should be a note on the log stating that zero observations were selected.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.