topic Re: Deleting empty rows and extra columns in New SAS User

Deleting empty rows and extra columns

Sami1532 — Sun, 01 Nov 2020 21:27:41 GMT

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!

Re: Deleting empty rows and extra columns

SASKiwi — Sun, 01 Nov 2020 21:37:34 GMT

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

Re: Deleting empty rows and extra columns

Sami1532 — Sun, 01 Nov 2020 21:43:34 GMT

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.

Re: Deleting empty rows and extra columns

SASKiwi — Sun, 01 Nov 2020 21:51:24 GMT

IMO a dumb assignment then 😀.

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

Re: Deleting empty rows and extra columns

Sami1532 — Sun, 01 Nov 2020 22:05:58 GMT

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?

Re: Deleting empty rows and extra columns

SASKiwi — Sun, 01 Nov 2020 22:12:38 GMT

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;

Re: Deleting empty rows and extra columns

andreas_lds — Mon, 02 Nov 2020 06:02:49 GMT

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;

Re: Deleting empty rows and extra columns

GoldingAngela — Fri, 06 Nov 2020 02:04:15 GMT

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;