topic Re: excel to sas in SAS Procedures

Re: excel to sas

Reeza — Thu, 02 Oct 2014 20:47:07 GMT

You don't use infile for Excel files, proc import or lib name are better options.

Provide more details of your issues and better yet a sample file

Or save as CSV and then write infile statements.

Re: excel to sas

Anotherdream — Thu, 02 Oct 2014 20:57:51 GMT

As reeza earlier pointed out, save the Excel file as a csv and then use an infile.

For excel you are stuck to proc import and/or libname statement. (unfortunately).

Note I imagine your values are getting truncated because Excel only uses the first 8 rows to determine variable type / length / etc.. Try adding the following options to your Proc Import and see if that fixes it.

guessingrows=X (i like 32000); The x is the number of rows you want the system to check to determine the variable type / length.

Re: excel to sas

rakeshvvv — Fri, 03 Oct 2014 12:17:09 GMT

I am using proc import and values are getting truncated at the N Column.......even though all decimals are not significant.....we would like to populate in the dataset the way it was collected.Attaching sample file.

Re: excel to sas

Reeza — Fri, 03 Oct 2014 13:34:54 GMT

I only see one decimal place?

My guess is you just need to change the display format, the underlying data is fine.

Post a proc contents on your imported data set.

Re: excel to sas

rakeshvvv — Fri, 03 Oct 2014 17:42:28 GMT

here is my proc content....

Alphabetic List of Variables and Attributes

#	Variable	Type	Len	Format	Informat	Label
17	COMMENT1	Char	118	$118.	$118.	COMMENT1
1	SUBJIDN	Num	8	BEST14.	SUBJIDN
6	VISITNUM	Num	8	BEST14.	VISITNUM
2	XBCATN	Num	8	BEST14.	XBCATN
5	XBNAM	Char	14	$14.	$14.	XBNAM
14	XBORRES	Char	14	$14.	$14.	XBORRES
15	XBORRESU	Char	14	$14.	$14.	XBORRESU
16	XBREASNDN	Char	14	$14.	$14.	XBREASNDN
4	XBREFID	Char	21	$21.	$21.	XBREFID
3	XBSPECN	Num	8	BEST14.	XBSPECN
11	XBTEST	Char	30	$30.	$30.	XBTEST
10	XBTESTCD	Char	16	$16.	$16.	XBTESTCD
13	XBTSTDL	Char	36	$36.	$36.	XBTSTDL
12	XBTSTDLN	Num	8	BEST15.	XBTSTDLN
7	XB_D	Num	8	BEST14.	XB_D
8	XB_M	Num	8	BEST14.	XB_M
9	XB_Y	Num	8	BEST14.	XB_Y

Re: excel to sas

Reeza — Fri, 03 Oct 2014 18:19:48 GMT

XBorres which corresponds to N column was brought in as a character so that's your first problem. If it has all the decimal places you can simply convert it to a numeric column using an input function.

Otherwise save as CSV and explicitly specify your type, though with that many digits you'll probably run into floating point errors anyways.

You could bring it in as character all across if you really wanted to keep it though Excel will generate the same errors, it just doesn't warn/tell you about it.