Help using Base SAS procedures

How to IMPORT and EDIT excel files

Reply
N/A
Posts: 0

How to IMPORT and EDIT excel files

Hey ppl ,

I'm new to SAS and I have a question .

I have imported an excel file into SAS using the following code .

PROC IMPORT OUT= WORK.auto1
DATAFILE= "C:\Chris\Aug09.xls"
DBMS=EXCEL REPLACE;
SHEET="Sheet1";
GETNAMES=YES;
MIXED=YES;
USEDATE=YES;
SCANTIME=YES;


RUN;
proc print data=demo111;
run;


The excel file has about 500 rows and I have certain rows which are empty in the actual Excel file.
I wanna know how to write the code in the Editor so that i will be able to delete the empty rows and print the rows with only with values.

I was wondering if there is anything using the if statement , u know something like :

if row=Null
DELETE obs or row ;



Please help me out as I've been stuck with this for the last 2 days and its very annoying.
N/A
Posts: 0

Re: How to IMPORT and EDIT excel files

Posted in reply to deleted_user
Please guys , any help is appreciated.

All i want to know is how to delete the empty rows and print the result out .

Thanks
Super Contributor
Super Contributor
Posts: 3,174

Re: How to IMPORT and EDIT excel files

Posted in reply to deleted_user
There is SAS-hosted documentation and supplemental technical and conference topic-related reference material on the SAS support http://support.sas.com/ website. This is basic DATA STEP programming here -- or PROC SQL, if you prefer.

SAS 9.2 Language Reference: Concepts - DATA Step Processing
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a001281588.htm

Programming with the SQL Procedure
http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001340018.htm


Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument to consider using:

data step programming site:sas.com
Super Contributor
Super Contributor
Posts: 3,174

Re: How to IMPORT and EDIT excel files

Here's a code snippet structure using the DATA step approach:

DATA ;
SET ;
IF THEN DELETE;
RUN;


Scott Barry
SBBWorks, Inc.
N/A
Posts: 0

Re: How to IMPORT and EDIT excel files

Thanks SBB .

In my excel sheet I have 4 columns .

In one of the columns 5 rows are empty whereas the subsequent rows are full.
I want to delete the whole row .

My question is , Do I use the If statement and set the condition as

IF (column=empty) then Delete; ?
SAS Super FREQ
Posts: 8,866

Re: How to IMPORT and EDIT excel files

Posted in reply to deleted_user
As an alternative to Scott's suggestion, you can apply a WHERE statement directly to the rows selected (or not selected) for PROC PRINT (or any other SAS procedure, such as PROC SORT, PROC MEANS, PROC FREQ, etc).

[pre]
proc print data=work.auto1;
where somecondition = somevalue and
othercondition = othervalue;
run;
[/pre]

Let's say, for example, that you have these columns or variables in your imported dataset:
NAME AGE GENDER AMOUNT

and that the valid rows all have some value in the NAME column. Then you could do this to only print/display the valid rows:
[pre]
proc print data=work.auto1;
where name gt ' ';
run;
[/pre]

....or whatever your condition is that reveals the valid rows.

cynthia
N/A
Posts: 0

Re: How to IMPORT and EDIT excel files

Posted in reply to Cynthia_sas
Hey Cynthia ,

Thanks for the help rendered .

I tried the above mentioned , but it did not work for me .

I have 4 colums NAME AGE HEIGHT DOB
I have all entries for the Name and DOB column .
Some entries are missing for AGE and some entries are missing for HEIGHT.

When I imported the file to SAS , I got everything from the excel sheet displayed in the SAS output window including the missing entries for AGE and HEIGHT.

I want to erase the whole row which does not have an entry in one of the columns so that when I see my output window in SAS , I want to make sure that I can see only valid entries.

And i also want to know how to output the entries in Excel and Text files.

This is the code so far that I've been using. I'm new to SAS and I have no knowledge about how to code in this .

PROC IMPORT OUT= WORK.auto1
DATAFILE= "C:\auto.xls"
DBMS=EXCEL REPLACE;
SHEET="auto1";
GETNAMES=YES;
MIXED=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
proc print data=auto1;
run;


Please help me with this code and any suggestions to improvise on my problem .
I appreciate all the help given out here.
SAS Super FREQ
Posts: 8,866

Re: How to IMPORT and EDIT excel files

Posted in reply to deleted_user
Hi:
Since you say you want to DELETE the rows, then you would have to use code, such as the sample shown in Scott's post. Something like this:
[pre]
data fixed;
set work.auto1;
if age = . or dob = . then delete;
run;
[/pre]

The above syntax assumes that both AGE and DOB were read as numeric variables. The '.' (period) is the way that SAS represents missing numeric variables when it internally stores the data.

After the data has been "cleaned up", you can write it back to Excel or to a CSV text file by using PROC EXPORT -- sort of the reverse of the PROC IMPORT that you've already coded. For more information about PROC EXPORT, refer to this site:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a000316288.htm

cynthia
N/A
Posts: 0

Re: How to IMPORT and EDIT excel files

Posted in reply to Cynthia_sas
This is my updated code . Its still not working. I checked it up in the SAS output window and im still havin those missin rows.


PROC IMPORT OUT= WORK.auto
DATAFILE= "C:\abcd\auto.xls"
SHEET="sheet1";
run;
proc print data=auto;
run;

DATA work.auto
SET "C:\ABCD\auto.xls"
IF age = '.' THEN DELETE;
RUN;


Any help is appreciated. Thanks-
SAS Super FREQ
Posts: 8,866

Re: How to IMPORT and EDIT excel files

Posted in reply to deleted_user
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]
Ask a Question
Discussion stats
  • 9 replies
  • 228 views
  • 0 likes
  • 3 in conversation