topic Re: how do I sort a data set by 2 specific criterias in 2 seperate variables in New SAS User

how do I sort a data set by 2 specific criterias in 2 seperate variables

doofst — Thu, 04 Oct 2018 01:16:20 GMT

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.

Re: how do I sort a data set by 2 specific criterias in 2 seperate variables

Astounding — Thu, 04 Oct 2018 01:39:38 GMT

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;

Re: how do I sort a data set by 2 specific criterias in 2 seperate variables

doofst — Thu, 04 Oct 2018 02:05:51 GMT

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;

Re: how do I sort a data set by 2 specific criterias in 2 seperate variables

Astounding — Thu, 04 Oct 2018 12:58:39 GMT

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.

Re: how do I sort a data set by 2 specific criterias in 2 seperate variables

ballardw — Thu, 04 Oct 2018 16:21:44 GMT

@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.