BookmarkSubscribeRSS Feed
shivakrishna
Fluorite | Level 6

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

17 REPLIES 17
Peter_C
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Quartz | Level 8

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
Quartz | Level 8

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
Fluorite | Level 6

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
Quartz | Level 8

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
Fluorite | Level 6

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
Rhodochrosite | Level 12

%include is for code  not data of that complexity

Reeza
Super User

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
Fluorite | Level 6

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
Super User

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
Quartz | Level 8

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 39977 views
  • 4 likes
  • 6 in conversation