I have created a sas data named tables.Q3 from an excel file containing hundreds of thousands of observations.
I would like to now extract specific observations that satisfy one condition in 1 variable and another condition in the other and print those data sets.
I first sorted the data set by one variable using:
PROC SORT DATA=tables.Q3 OUT=tables.Q4;
BY start_position;
RUN;
this produced a sas data set that sorted the start_code (which is a numeric) variable.
I want to now print from the dataset tables.Q4 a table that will only include those observations that satisfy start_position=5000 and variable 2 (which is a datetime variable in the form of DDMMMYYY HH:MM:SS) as one specific date (i.e 20OCT17).
Thanks in advance for the help.
The right statement depends on whether VARIABLE_2 is truly a SAS datetime value, or whether it is a character string. Either set of conditions is easy, and you can test easily enough which of these will work:
proc print data=q4;
* For true datetimes: ;
where start_position=5000 and datepart(variable_2) = '20oct2017'd;
* For character strings that contain a two-digit year: ;
where start_position=5000 and variable_2 =: '20OCT17';
* For character strings that contain a four-digit year: ;
where start_position=5000 and variable_2 =: '20OCT2017';
run;
The original excel file is formatted with the dates as follows:
2017-04-15 1:30 |
2017-04-15 2:50
|
Once I imported them into a sas data set they look like this:
15APR17:00:01:00
Now I used what you suggested and for all three the error message i'm receiving is the following:
831 where start_position=6008 and variable_2=:'30APR17';
ERROR: WHERE clause operator requires compatible variables.
832 run;
You'll have to find out a little more about the data then. After importing the data to SAS, run a PROC CONTENTS on the SAS data set and look at the characteristics of VARIABLE_2.
@doofst wrote:
I have created a sas data named tables.Q3 from an excel file containing hundreds of thousands of observations.
I would like to now extract specific observations that satisfy one condition in 1 variable and another condition in the other and print those data sets.
I first sorted the data set by one variable using:
PROC SORT DATA=tables.Q3 OUT=tables.Q4;
BY start_position;
RUN;this produced a sas data set that sorted the start_code (which is a numeric) variable.
I want to now print from the dataset tables.Q4 a table that will only include those observations that satisfy start_position=5000 and variable 2 (which is a datetime variable in the form of DDMMMYYY HH:MM:SS) as one specific date (i.e 20OCT17).
Thanks in advance for the help.
Are selecting by the datetime? or just want to display the datetime as a date in that format?
Compare the code snippet you say generated an error:
831 where start_position=6008 and variable_2=:'30APR17'; ERROR: WHERE clause operator requires compatible variables. 832 run;
with @Astounding's suggestion:
where start_position=5000 and datepart(variable_2) = '20oct2017'd;
DATE literals are quoted values ind date7 or date9 appearance followed by a D to tell SAS this intended to be used as a date literal. Without the D it is treated a simple character value and generates the error comparing numeric to character.
The function DATEPART tells SAS to only use the Date portion of a datetime value. The =: is right out as you want the date literal numeric version.
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.