topic Re: Deleting empty rows in SAS Programming

Deleting empty rows

sas_user4 — Wed, 12 Apr 2017 22:22:31 GMT

Hi,

I am importing excel into SAS. The excle sheet has 181 rows (first row for variables' names) but I get 189 rows when importing to SAS, this means I have 8 empty rows (see below). I see that the Excel sheet goes to 189 (meaning it seems formatted this way). Here is the Code I am using:

PROC IMPORT OUT= WORK.dataset DATAFILE= "C:\proj.xlsx"
DBMS=xlsx REPLACE;
GETNAMES=YES;
RUN;

How can I tell SAS to import only the rows with valid observations?

SAS Output

180	180	0	20	158	1	0	0	1
181	.	.	.	.	.	.	.	.
182	.	.	.	.	.	.	.	.
183	.	.	.	.	.	.	.	.
184	.	.	.	.	.	.	.	.
185	.	.	.	.	.	.	.	.
186	.	.	.	.	.	.	.	.
187	.	.	.	.	.	.	.	.
188	.	.	.	.	.	.	.	.
189	.	.	.	.	.	.	.	.

Re: Deleting empty rows

art297 — Wed, 12 Apr 2017 22:55:07 GMT

Are all of the fields numeric, character or a mixture of both?

Art, CEO, AnalystFinder.com

Re: Deleting empty rows

art297 — Wed, 12 Apr 2017 23:03:01 GMT

I'm not aware of a way to do it during import but, regardless of whether you have numeric variables, character variables, or a mixture of both, the following should work after you've imported the file:

data have;
  input a b $ c d $ e f $ g;
  cards;
1 2 3 4 5 6 6
1 . 3 4 5 6 7
1 2 . 4 5 6 7
. . . . . . .
. . . . . . .
. . . . . . .
;
data want;
  set have;
  if not (nmiss(of a--g) eq 7);
run;

Art, CEO, AnalystFinder.com

Re: Deleting empty rows

sas_user4 — Wed, 12 Apr 2017 23:06:38 GMT

Thank you. It is a misture of both.

Re: Deleting empty rows

Ksharp — Thu, 13 Apr 2017 12:39:44 GMT


if not (Cmiss(of a--g) eq 7);

Re: Deleting empty rows

sas_user4 — Thu, 13 Apr 2017 13:31:41 GMT

Thank you both for your replies. I am sorry, I am not familiar with these commands. Can you please modify them for the purpose of my code? What does

if not (Cmiss(of a--g) eq 7);

Cmiss or Nmiss mean? and a--g or eq 7??

Re: Deleting empty rows

Ksharp — Thu, 13 Apr 2017 13:34:46 GMT

CMISS() is for both numeric and characher variable,

while NMISS() is only for numeric variable.

Re: Deleting empty rows

sas_user4 — Thu, 13 Apr 2017 13:43:48 GMT

The following code worked just fine:

data try;
set new;
if cmiss(of _all_) then delete;
run;

Thanks!

Re: Deleting empty rows

art297 — Thu, 13 Apr 2017 13:56:44 GMT

I agree that cmiss would be more appropriate but, in this case, both cmiss and nmiss provide the same result.

Art, CEO, AnalystFinder.com

Re: Deleting empty rows

ballardw — Thu, 13 Apr 2017 14:45:42 GMT

Be prepared if using Proc Import and Excel files that the exact same cause for you "blank" rows will also create "blank" variables. The issue here is that Excel will consider any row or column that had any value entered into any cell init, even if deleted, as "active". The engine that SAS talks to with Excel via Proc Import is told by Excel that the cells should have values.

You will at some time likely get varaibles named VARxx with no data.

Re: Deleting empty rows

ericdrosano — Fri, 12 Jan 2018 16:38:18 GMT

Simple and effective. I've now saved this in my codes folder. Thank you very much!

Re: Deleting empty rows

mkeintz — Mon, 15 Jan 2018 23:44:41 GMT

In excel select rows 181-189. Delete them. Save the excel file. Excel now thinks the end-of-sheet is row 180.. Re-import.

Re: Deleting empty rows

Norman21 — Thu, 06 Aug 2020 07:08:40 GMT

@sas_user4 wrote:

The following code worked just fine:

data try;
set new;
if cmiss(of _all_) then delete;
run;

Thanks!

The above code will delete a row if any of the values are blank. I thought you wanted to delete a row of all of the values are blank.

180	180	0	20	158	1	0	0	1
181	.	.	.	.	.	.	.	.
182	.	.	.	.	.	.	.	.
183	.	.	.	.	.	.	.	.
184	.	.	.	.	.	.	.	.
185	.	.	.	.	.	.	.	.
186	.	.	.	.	.	.	.	.
187	.	.	.	.	.	.	.	.
188	.	.	.	.	.	.	.	.
189	.	.	.	.	.	.	.	.

180	180	0	20	158	1	0	0	1
181	.	.	.	.	.	.	.	.
182	.	.	.	.	.	.	.	.
183	.	.	.	.	.	.	.	.
184	.	.	.	.	.	.	.	.
185	.	.	.	.	.	.	.	.
186	.	.	.	.	.	.	.	.
187	.	.	.	.	.	.	.	.
188	.	.	.	.	.	.	.	.
189	.	.	.	.	.	.	.	.

180	180	0	20	158	1	0	0	1
181	.	.	.	.	.	.	.	.
182	.	.	.	.	.	.	.	.
183	.	.	.	.	.	.	.	.
184	.	.	.	.	.	.	.	.
185	.	.	.	.	.	.	.	.
186	.	.	.	.	.	.	.	.
187	.	.	.	.	.	.	.	.
188	.	.	.	.	.	.	.	.
189	.	.	.	.	.	.	.	.