Importing Excel file by using datastep

[shivakrishna]

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

[Peter_C]

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)

[shivakrishna]

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

[jwillis]

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#n18jyszn33umngn14czw2...

[jwillis]

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;

[shivakrishna]

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

[jwillis]

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.

[shivakrishna]

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

[Peter_C]

%include is for code  not data of that complexity

[Reeza]

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.

[shivakrishna]

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

[Reeza]

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

[jwillis]

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;

[jwillis]

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.

[RW9]

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.