topic Re: Importing Excel file by using datastep in SAS Programming

Importing Excel file by using datastep

shivakrishna — Tue, 06 May 2014 18:58:40 GMT

Can you please provide me the suggestion for importing the excel file.

while importing the excel file by using datastep, i"m getting error and in the dataset having different launguge.

can anyone provide need full help for the same

regards

Shiva

Re: Importing Excel file by using datastep

Peter_C — Tue, 06 May 2014 19:06:23 GMT

Licence SAS/ACCESS to PC Files

and you might be able to treat a named range in excel just like a dataset in a datastep

(using LIBNAME access method)

Re: Importing Excel file by using datastep

shivakrishna — Tue, 06 May 2014 19:14:24 GMT

but i have a licence for SAS/ACCESS to PC Files.

Can you please elaborate why this type of different language is coming for character variables and all numeric i'm getting missing values..

Thanks

Shiva

Re: Importing Excel file by using datastep

jwillis — Tue, 06 May 2014 19:15:34 GMT

Shiva,

I do not have enough information about your problem. I do have code that reads an excel spreadsheet manually though. Your spreadsheet must be open for this code to work.

/*** clif ***/
%let sheet=HealthCosts;
%let strt=2; /* data starts here */
%let stp=25; /* data ends somewhere before here */

%let hospdir=h:\temp\;
%let wkbk=Robinsondata.xlsx;

options mlogic mprint macrogen symbolgen;

/* put in your starting column and ending column */

data _null_;
call symput ("opensht",
"'"||'EXCEL|'||"&hospdir"||"[&wkbk.]"||"&sheet."||'!r'||
"&strt"||'c1:r'||"&stp"||'c2'||"'");
run;

/* view the macro resolutions. Make sure they work */
%put &opensht;

/* open the workbooks */
/* all workbooks and worksheets must exist or this will not work */

filename incodes dde &opensht notab;

                  /* facility sheet */
    data home.&sheet.;
   length costsx $16 costs 8 c1 $16 year c2 $4;
    infile incodes dsd delimiter='09'x noPAD ls=1500 recfm=fb truncover;
    input   C1 $
    C2 $
    ;
    if (C1 in: ('      ')) and (C2 in: ('      '))
    then delete;
     costsx = trim(left(c1));
        year = trim(left(c2));
   costs = input(costsx,8.);

   drop c1 c2 costsx;
   run;

proc print data=home.&sheet.(obs=20);
run;

Re: Importing Excel file by using datastep

jwillis — Tue, 06 May 2014 19:23:55 GMT

Shiva,

Proc Import reads the first few cells of a column to determine if a column is all numeric or all character**. If you have mixed values, try to sort your data so that Proc Import will make the whole column character.

**http://support.sas.com/documentation/cdl/en/proc/66663/HTML/default/viewer.htm#n18jyszn33umngn14czw2qfw7thc.htm

Re: Importing Excel file by using datastep

shivakrishna — Tue, 06 May 2014 19:30:17 GMT

Thank you for the code, but i know in different methods to import the excel files but my question is while importing the excel file by using the data step I'm getting different language for character variables and all numeric variables are missing and also getting error like lost cards. i want to know why this is happening in only datastep.

For example:

data class;

infile "path/class.xls";

input name$ sex$ age height weight;

run;

Thanks

Shiva

Re: Importing Excel file by using datastep

jwillis — Tue, 06 May 2014 19:33:51 GMT

I need to see examples of what you see and what you want to see. Normally, the problems are with your excel file being in a format/fashion/layout that is different than what SAS 'defaults' expect. Your code appears to me to be relying on the SAS defaults to read your excel file correctly.

Re: Importing Excel file by using datastep

Peter_C — Tue, 06 May 2014 19:35:11 GMT

%include is for code not data of that complexity

Re: Importing Excel file by using datastep

Reeza — Tue, 06 May 2014 19:49:22 GMT

I don't understand. Why are you trying to read an Excel file via a data step?

Data step is for CSV and TXT files generally, not Excel files.

All of the formatting that Microsoft attaches to the XLS files make it difficult to read via data step. If you need a way that doesn't include proc import you can try some data base options, but it helps to know why.

Re: Importing Excel file by using datastep

shivakrishna — Tue, 06 May 2014 19:50:00 GMT

Please see the below example:

proc export outfile='C:\class.xls' data=sashelp.class;

run;

4 data class1;

5 infile "C:\class.xls";

6 input name$ sex$ age height weight;

7 run;

NOTE: The infile "C:\class.xls" is:

Filename=C:\class.xls,

RECFM=V,LRECL=256,File Size (bytes)=11776,

Last Modified=07May2014:01:13:55,

Create Time=07May2014:01:13:54

NOTE: LOST CARD.

name=ÐÏ à¡± sex= age=. height=. weight=. _ERROR_=1 _N_=1

NOTE: 1 record was read from the infile "C:\class.xls".

The minimum record length was 6.

The maximum record length was 6.

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.

NOTE: The data set WORK.CLASS1 has 0 observations and 5 variables.

NOTE: DATA statement used (Total process time):

real time 0.29 seconds

cpu time 0.07 seconds

Re: Importing Excel file by using datastep

jwillis — Tue, 06 May 2014 19:50:01 GMT

Shiva,

Does this do what you need? The code is untested.

data class;

infile "path/class.xls";

format name $50. sex $2. age height weight 8.;

input c1 $ c2 $ c3 $ c4 $ c5$; /*** read all values as character ***/

name    = strip(c1);
sex       = strip(c2);
age       = input(strip(c3),8.);           /*** convert character numbers into numeric numbers ***/
height   = input(strip(c4),8.);
weight = input(strip(c5),8.);

run;

Re: Importing Excel file by using datastep

shivakrishna — Tue, 06 May 2014 19:56:46 GMT

you are correct reeza, but why the datastep is not reading the data from excel file.

Possible can you please explain in detail why the datastep is not reading excel file.

Thanks

shiva

Re: Importing Excel file by using datastep

Reeza — Tue, 06 May 2014 20:02:56 GMT

Because Excel has add a whole bunch of encoding around the variables and SAS doesn't know how to parse through it.

What you're doing is the equivalent of trying to open a .XLS file with a text editor. Try that and see what happens.

To speak the same language you need a different tool, ie proc import

Re: Importing Excel file by using datastep

jwillis — Wed, 07 May 2014 12:27:08 GMT

Shiva,

Please consider the solution I've documented in the attached txt file. Many government and private sources sent us Excel files as the documented record of source. I was forced to find a way to read the source EXCEL document as it was delivered. The source documents had odd characters and stray values and other issues so I was forced to read only the cells and columns that contained information. The solution in the attached txt file reads all the cell values as txt then converts the text values to their proper formats.

Re: Importing Excel file by using datastep

RW9 — Wed, 07 May 2014 12:40:14 GMT

Hi,

Whilst your solution is very nice, I wouldn't recommend this approach. There are several reasons for this. Mainly DDE is quite an old technology now, which MS includes only for historical purposes, and SAS supports only for historical purposes. It is no longer updated or advanced and you may find support dropped for it completely in newer versions. There are many methods for importing data from Excel, Save the Excel file as CSV then use that, proc import, or by doing a:

libname mybook xls "....\someexcelfile.xls";

You can then use sas datasteps to read ranges of data direct from the excel file. See:

http://www2.sas.com/proceedings/sugi31/024-31.pdf

For further guidance on the libname engine.

Re: Importing Excel file by using datastep

jwillis — Wed, 07 May 2014 18:13:36 GMT

HI RW9,

SAS has the agility to do anything. There are as many SAS coding styles as there are people coding SAS. The DDE solution works today and is a solution that will also write to a specific cell in any excel spreadsheet. My coding style was developed to read from and write to specific sheets and cells in "Data Source Record" worksheets. I have encountered all of the items listed in the SUGI proceedings paper link at least once and I am certain that once the DDE solution is no longer viable, the brilliant folks at SAS will be able to create a solution to replace the DDE method**.

SAS Community is a place where a community can come together to learn. I monitor the community to learn new ideas, methods and solutions. I believe the SAS DDE solution was built to solve the problem "...Excel has...a whole bunch of encoding around the variables and SAS doesn't know how to parse through it...." I also believe that SAS will not let SAS users down. I read your replies and I thank you for those replies. I learn a great deal from all who reply.

**"Note that DDE may be used to create, delete, or rename spreadsheets, as well as to format worksheets and enter and delete formulas."

Re: Importing Excel file by using datastep

RW9 — Thu, 08 May 2014 08:00:03 GMT

Whilst I am not disagreeing with you, MS has moved to the Open Office XML based format now, hence any application type of coding which can read and write text to a file should be able to do most things in Excel. I can see one area where this is not the case, VBA macros are still held in a proprietary binary file within the XLSX. However saying that, you could build the VBA, compile that, and then copy the file from the old XLSX into your created file before zipping up. Note in this instance I had created all the structure, XML files and definitions manual and packaged them into a zip to create the XLSX rather than using tagsets.

Re: Importing Excel file by using datastep

PoojaM — Sat, 28 May 2016 03:28:20 GMT

Try Using something like this :

filename soy dde 'Excel|C:\Courses\Independent Study\Data\[SOYREF95.XLS]REF95!R2C2:R229C10';
data bean;
infile soy missover;
input labid $ month reps moisture abn dead abn1 abn2 abn3;
run;
proc print data=bean; run;

Link : http://www.ats.ucla.edu/stat/sas/library/SASRead_os.htm#ReadingExcelFilesIntoSAS

Thanks!