Write and run SAS programs in your web browser

Conditional DROP statement

Accepted Solution Solved
Reply
Highlighted
Regular Contributor
Posts: 184
Accepted Solution

Conditional DROP statement

Hello,

 

New SAS user here. I have many missing observations in my dataset (states which didn't report arrest data according to the codebook), and I am trying to delete these missing observations so that they don't influence my data. The variable of interest is called GRNDTOT. I would like to do something along the lines of:

 

data paper.arrests_schools_popc;
if GRNDTOT=0 then delete;
run;

 

But this isn't working. The key concern here is to make sure the rows and columns remain aligned (rather than truncating the GRNDTOT column (therefore confusing the whole dataset) but to drop the FIPS codes without GRNDTOT data. Any help is appreciated!

 

Thanks, 

 

SAstuck


Accepted Solutions
Solution
‎12-14-2017 06:51 PM
Super User
Super User
Posts: 7,860

Re: Conditional DROP statement

Since you posted a word file with a graph in it instead of showing any data it is hard to know exactly what you want.  If you want to post a photo of your data using the Photos icon on the menu bar.  If you want to post examples of your data use the Insert Code icon instead and post a data step to create a sample of your data.

 

Your code should DELETE observations where the GRAND_TOTAL variable is zero, but you did not apply it to any data.  So you just created an empty dataset with only one variable. (NOTE that DROP is used to eliminate variables, not observations.)

 

But are you sure you don't want to just code the values to missing instead?  That way the observation is not deleted just because one variable has a missing value.  So if for some reason your original data was coded with zero to represent misisng data then you could use a step like this to convert the zeros to missing values instead.  Most SAS procedures will handle missing values. 

 

data want ;
  set have ;
  if grand_total=0 then grand_total=.;
run;

 

 

View solution in original post


All Replies
Super User
Posts: 22,857

Re: Conditional DROP statement

Your code will probably work if you add a SET statement so it has input data to work. Otherwise the concept/approach appears correct.

 


sastuck wrote:

Hello,

 

New SAS user here. I have many missing observations in my dataset (states which didn't report arrest data according to the codebook), and I am trying to delete these missing observations so that they don't influence my data. The variable of interest is called GRNDTOT. I would like to do something along the lines of:

 

data paper.arrests_schools_popc;
if GRNDTOT=0 then delete;
run;

 

But this isn't working. The key concern here is to make sure the rows and columns remain aligned (rather than truncating the GRNDTOT column (therefore confusing the whole dataset) but to drop the FIPS codes without GRNDTOT data. Any help is appreciated!

 

Thanks, 

 

SAstuck


 

PROC Star
Posts: 1,190

Re: Conditional DROP statement

And by missing, you mean 0 values I take it? Smiley Happy

 

Your code seems legit. You could also do something like this

 

data paper.arrests_schools_popc;
	set YourData(where=(GRNDTOT ne 0));
run;

 

 

Solution
‎12-14-2017 06:51 PM
Super User
Super User
Posts: 7,860

Re: Conditional DROP statement

Since you posted a word file with a graph in it instead of showing any data it is hard to know exactly what you want.  If you want to post a photo of your data using the Photos icon on the menu bar.  If you want to post examples of your data use the Insert Code icon instead and post a data step to create a sample of your data.

 

Your code should DELETE observations where the GRAND_TOTAL variable is zero, but you did not apply it to any data.  So you just created an empty dataset with only one variable. (NOTE that DROP is used to eliminate variables, not observations.)

 

But are you sure you don't want to just code the values to missing instead?  That way the observation is not deleted just because one variable has a missing value.  So if for some reason your original data was coded with zero to represent misisng data then you could use a step like this to convert the zeros to missing values instead.  Most SAS procedures will handle missing values. 

 

data want ;
  set have ;
  if grand_total=0 then grand_total=.;
run;

 

 

Super User
Posts: 9,600

Re: Conditional DROP statement

Do not use MS Office files for posting code, log or data.

Convert SAS datasets to a data step by using the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat..., and post the resulting code according to the advice in https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce. Use the same method for posting any code, and use the {i} icon for logs or other text that needs a fixed-width font and the formatting (indentation) preserved.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 437 views
  • 4 likes
  • 5 in conversation