Deleting empty rows and extra columns

[Sami1532]

Hi, 

I'm currently trying to use SAS to get rid of the empty excel rows while also deleting some additional columns that I don't need. 

Is there a generic formula I can use to do this? I'm also using the SAS University edition, not sure if that'll change anything too much. 

Thanks! 

[SASKiwi]

Why not just fix your Excel spreadsheet and then re-import your data?

[Sami1532]

I have to do this as part of an assignment for my professor so he wants us to use SAS. I would much rather use excel. 

[SASKiwi]

IMO a dumb assignment then 😀.

Investigate the DROP statement for deleting extra columns and the WHERE statement for deleting extra rows. 

[Sami1532]

It really is, I'm honestly losing my mind because I don't know how to use SAS. 

I'll try to use the DROP function. Quick question a few of my classmates have used this: 

options missing = '  '; 

data examtwo;

set examtwo; 

if missing(cats(of_all_)) then delete; 

run;

- BUT this isn't working for me. It just deletes all my data. Not sure if this is also a possible option? 

[SASKiwi]

There's a typo in your code - add a space between of and _all_. Without seeing your data not sure it will work though:

options missing = '  '; 

data examtwo;

set examtwo; 

if missing(cats(of _all_)) then delete; 
run;

DROP is a statement not a function:

data want;
  drop COLA COLB;
  set have;
run;

[andreas_lds]

Depending on the number of variables you need to drop, it could be easier to use the keep statement instead of drop. With the following keep-statement all variables from FirstVar to LastVar are kept, all vars before FirstVar and after LastVar are dropped.

keep FirstVar--LastVar;

[GoldingAngela]

Hello, 

If they are simply just empty rows and you know the range that you want to remove, try this:

DATA WORK.Want;

SET WORK.Have;

IF _N_ IN (row#:row#) THEN DELETE;

RUN;