04-12-2017 06:22 PM
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"
How can I tell SAS to import only the rows with valid observations?
04-12-2017 07:03 PM
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
04-13-2017 09:31 AM
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??
04-13-2017 10:45 AM
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.