05-06-2014 02:58 PM
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
05-06-2014 03:06 PM
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)
05-06-2014 03:14 PM
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..
05-06-2014 03:23 PM
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.
05-06-2014 03:15 PM
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 strt=2; /* data starts here */
%let stp=25; /* data ends somewhere before here */
options mlogic mprint macrogen symbolgen;
/* put in your starting column and ending column */
call symput ("opensht",
/* view the macro resolutions. Make sure they work */
/* open the workbooks */
/* all workbooks and worksheets must exist or this will not work */
filename incodes dde &opensht notab;
/* facility 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 $
if (C1 in: (' ')) and (C2 in: (' '))
costsx = trim(left(c1));
year = trim(left(c2));
costs = input(costsx,8.);
drop c1 c2 costsx;
proc print data=home.&sheet.(obs=20);
05-06-2014 03:30 PM
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.
input name$ sex$ age height weight;
05-06-2014 03:33 PM
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.
05-06-2014 03:50 PM
Please see the below example:
proc export outfile='C:\class.xls' data=sashelp.class;
4 data class1;
5 infile "C:\class.xls";
6 input name$ sex$ age height weight;
NOTE: The infile "C:\class.xls" is:
RECFM=V,LRECL=256,File Size (bytes)=11776,
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
05-06-2014 03:49 PM
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.
05-06-2014 03:56 PM
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.
05-06-2014 04:02 PM
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
05-06-2014 03:50 PM
Does this do what you need? The code is untested.
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.);
05-07-2014 08:27 AM
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.
05-07-2014 08:40 AM
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:
For further guidance on the libname engine.