BookmarkSubscribeRSS Feed
sas_user4
Obsidian | Level 7

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 . . . . . . . .
12 REPLIES 12
art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

art297
Opal | Level 21

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

 

sas_user4
Obsidian | Level 7

Thank you. It is a misture of both.

Ksharp
Super User

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

sas_user4
Obsidian | Level 7

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?? 

Ksharp
Super User

CMISS() is for both numeric and characher variable,

while NMISS() is only for numeric variable.

sas_user4
Obsidian | Level 7

The following code worked just fine:

 

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

 

Thanks!

art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

ericdrosano
Obsidian | Level 7

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

Norman21
Lapis Lazuli | Level 10

@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.

Norman.
SAS 9.4 (TS1M6) X64_10PRO WIN 10.0.17763 Workstation

ballardw
Super User

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.

 

 

mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 11232 views
  • 4 likes
  • 7 in conversation