Hi:
Look carefullly at the SET statement in my code compared to your SET statement. You seem to want to use AUTO.XLS in a SET statement. This is inappropriate.
Think of your program steps as being separate steps in a recipe:
1) import AUTO.XLS into SAS. Cook for a while using PROC IMPORT. Out comes WORK.AUTO -- now a SAS dataset, but with bad rows (At this point, AUTO.XLS is still bad and the SAS -copy- of AUTO.XLS which is named WORK.AUTO is also bad)
2) How to fix WORK.AUTO to make it taste better??? Mix it into a DATA step program: In goes WORK.AUTO, shake the data through an IF statement to filter out the bad rows, cook for a while in the DATA step program. Out comes WORK.FIXED (hopefully with the bad rows gone if your logic was correct)
(Note that I said, how to fix WORK.AUTO -- NOT how to fix AUTO.XLS. If you need to fix AUTO.XLS, fix it with Excel and resave using Excel.)
3) NOW, print WORK.FIXED and serve it to your folks in a report.
Just like a recipe, every SAS step is either a procedure or program that has ingredients (INPUT DATA or EXTERNAL FILE) and the finished product (OUTPUT SAS DATASET (usually)). The OUTPUT from one step generally becomes the INPUT to another step. If you are happy with WORK.FIXED and wanted to write it BACK to Excel, then you would use PROC EXPORT on WORK.FIXED, to export the now, cleaned-up and cooked data back into Excel.
If you really did submit that code (as posted), did you look at your SAS log??? I'm guessing that there were a LOT of errors. Note that the syntax for a DATA step program is different than PROC IMPORT. If you look at my previous posting, you will see that I ended each statement with a semi-colon -- so the DATA statement ends with a semi-colon and the SET statement ends with a semi-colon, as well as the IF statement and the RUN statement.
Also, the reason a SET statement -is- called a SET statement is that it is pointing to a SAS dataSET. The file, AUTO.XLS is -not- a SAS dataset. However, WORK.AUTO --which was created by PROC IMPORT-- is a SAS dataset. It's a copy of AUTO.XLS -- but in SAS proprietary format.
Also, the PROC PRINT after the PROC IMPORT is only going to show you the "bad" auto data that you just IMPORTED, if you used code more similar to my code snippet, then your DATA step and PROC PRINT would be:
[pre]
** PROC IMPORT step the same as previous;
data fixed;
set work.auto;
if age = . or dob = . then delete;
run;
proc print data=work.fixed;
title 'This is the fixed data';
run;
[/pre]
cynthia
[pre]
*** Annotated code ***
PROC IMPORT OUT= WORK.auto <--WORK.AUTO is the OUTPUT dataset
DATAFILE= "C:\abcd\auto.xls" <-- AUTO.XLS in the INPUT Excel Workbook
SHEET="sheet1"; <---point to the sheet in the workbook
run; < -- the step boundary that causes PROC IMPORT to "run" or execute
proc print data=auto; <-- Using WORK.AUTO with PROC PRINT will
still show the "bad" data
run; <--- the step boundary
data fixed; <---- WORK.FIXED is the output data set
set work.auto; < --- WORK.AUTO is the input data set
if age = . or dob = . then delete; < --- this is the filter
run; < --- this is the step boundary and end of the program,
which implicitly causes each observation that passes
the filter to be output to WORK.FIXED
proc print data=work.fixed; <--- WORK.FIXED is the INPUT dataset
title 'This is the fixed data'; <--- this is the report title
run; <--- this is the step boundary
[/pre]