DATA Step, Macro, Functions and more

Keep 2 Variables based on conditions

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Keep 2 Variables based on conditions

Hey guys,

 

I have a dataset with 9 Variables and 300 Observations. Now I want to keep some Observations based on some conditions.

I have a variable Notificationdate and I only need Observations between the 01/01/2014 and the 31/12/2016 and Observations where Art_der_Anzeige = Erstanzeige. I tried so many options but neither would work. Thank you!

 

data saspms.datensatz_new;
set saspms.datensatz_pms_neu;
where notificationdate <= 01/01/2014 & >= 31/12/2016 ;

where Art_der_Anzeige= Erstanzeige;
run;

 

or

 

data saspms.datensatz_new;
set saspms.datensatz_pms_neu;
keep if notificationdate <= 01/01/2014 & >= 31/12/2016 and keep if Art_der_Anzeige= Erstanzeige;
run;

 

 


Accepted Solutions
Solution
‎01-31-2018 08:24 AM
Super User
Posts: 9,611

Re: Keep 2 Variables based on conditions

[ Edited ]
Posted in reply to marysmith

Then your first step should be converting your dates stored as character into real SAS date values. Since we now need to work with a newly created variable, switch from a where condition to a subsetting if:

data saspms.datensatz_new;
set saspms.datensatz_pms_neu;
format n_date ddmmyy10.;
n_date = input(notificationdate,ddmmyy10.);
if '01jan2014'd <= n_date <= '31dec2016'd and Art_der_Anzeige = "Erstanzeige";
run;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,611

Re: Keep 2 Variables based on conditions

Posted in reply to marysmith

Do this:

data saspms.datensatz_new;
set saspms.datensatz_pms_neu;
where '01jan2014'd <= notificationdate <= '31dec2016'd and Art_der_Anzeige = "Erstanzeige";
run;

Note:

  • character literals must be enclosed in quotes
  • date literals are specified in the above manner; other date notations only work in an input function with a proper informat
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

Re: Keep 2 Variables based on conditions

Posted in reply to KurtBremser
Thank you for your quick respond. But unfortunately I got an error message:
ERROR: WHERE clause operator requires compatible variables.
What does it mean?

Thank you so much!
Super User
Posts: 9,611

Re: Keep 2 Variables based on conditions

Posted in reply to marysmith

Maxim 3: Know your data.

Run a proc contents on your dataset to determine variable types and assigned formats.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

Re: Keep 2 Variables based on conditions

Posted in reply to KurtBremser
Yeah I did that. Both a character Variables..
Solution
‎01-31-2018 08:24 AM
Super User
Posts: 9,611

Re: Keep 2 Variables based on conditions

[ Edited ]
Posted in reply to marysmith

Then your first step should be converting your dates stored as character into real SAS date values. Since we now need to work with a newly created variable, switch from a where condition to a subsetting if:

data saspms.datensatz_new;
set saspms.datensatz_pms_neu;
format n_date ddmmyy10.;
n_date = input(notificationdate,ddmmyy10.);
if '01jan2014'd <= n_date <= '31dec2016'd and Art_der_Anzeige = "Erstanzeige";
run;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

Re: Keep 2 Variables based on conditions

Posted in reply to KurtBremser
Thank you so much! That helped me a lot.
But what if there is even more conditions? Just realized that I need something else. My Variable Art_der_Anzeige has 4 values: Erstanzeige, Änderungsanzeige, Beendigung, Abschlussbericht, I need all the cases where I have "Erstanzeige" between 2014-2016 and Änderungsanzeige/Abschlussbericht/Beendigung after 2014.
I tried it like this but it didnt work Smiley Sad
data saspms.versuch;
set saspms.datensatz_pms_neu;
format n_date1 ddmmyy10.;
n_date1 = input(notificationdate,ddmmyy10.);
if "01jan2014"d <= n_date1 Art_der_Anzeige = "Erstanzeige" or if Art_der_Anzeige NE "Erstanzeige" and n_date >= "01jan2014"d ;
Super User
Posts: 9,611

Re: Keep 2 Variables based on conditions

[ Edited ]
Posted in reply to marysmith

The syntax for the subsetting if is this:

if condition ;

A condition is a piece of boolean logic; boolean operators are not, and, or.

So your second if is wrong syntax, it should be

if "01jan2014"d <= n_date1 <= '31dec2016'd and Art_der_Anzeige = "Erstanzeige" or Art_der_Anzeige ne "Erstanzeige" and n_date >= "01jan2014"d;

Note that, in boolean logic, not is evaluated before and, and and before or.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 229

Re: Keep 2 Variables based on conditions

Posted in reply to marysmith

The KEEP statement is not used to limit the number of observations, but the number of variables. But the WHERE statement does what you want. Only you should limit it to one statement, which combines the two conditions. And then you need to change your SAS date constants (I assume that your dates are SAS dates in the format DDMMYY10.):

data saspms.datensatz_new;
  set saspms.datensatz_pms_neu;
  where '01JAN2014'd <= notificationdate <= '31DEC2016'D 
    and Art_der_Anzeige= Erstanzeige;
run;

 

 

Contributor
Posts: 37

Re: Keep 2 Variables based on conditions

[ Edited ]
Posted in reply to marysmith

data saspms.datensatz_new;
set saspms.datensatz_pms_neu;
where notificationdate between  "01/01/2014"d and  "31/12/2016"d and Art_der_Anzeige="Erstanzeige";
run;

Contributor
Posts: 37

Re: Keep 2 Variables based on conditions

Posted in reply to marysmith

 

proc sql;

create table saspms.datensatz_new as 

select * from saspms.datensatz_pms_neu where

notificationdate between("01/01/2014"d and  "31/12/2016"d) and Art_der_Anzeige="Erstanzeige";

quit;

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 134 views
  • 1 like
  • 4 in conversation