Help using Base SAS procedures

Re: excel to sas

Reply
Super User
Posts: 19,878

Re: excel to sas

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.

Super Contributor
Posts: 418

Re: excel to sas

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.

Frequent Contributor
Posts: 145

Re: excel to sas

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.

Super User
Posts: 19,878

Re: excel to sas

Posted in reply to rakeshvvv

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.

Frequent Contributor
Posts: 145

Re: excel to sas

here is my proc content....

        Alphabetic List of Variables and Attributes

                              #Variable TypeLenFormat InformatLabel

                            
17COMMENT1 Char118$118.  $118.   COMMENT1
                              1SUBJIDN  Num   8BEST14.            SUBJIDN 
                              6VISITNUM Num   8BEST14.            VISITNUM
                              2XBCATN   Num   8BEST14.            XBCATN  
                              5XBNAM    Char 14$14.   $14.    XBNAM   
                             14XBORRES  Char 14$14.   $14.    XBORRES 
                             15XBORRESU Char 14$14.   $14.    XBORRESU
                             16XBREASNDNChar 14$14.   $14.    XBREASNDN
                              4XBREFID  Char 21$21.   $21.    XBREFID 
                              3XBSPECN  Num   8BEST14.            XBSPECN 
                             11XBTEST   Char 30$30.   $30.    XBTEST  
                             10XBTESTCD Char 16$16.   $16.    XBTESTCD
                             13XBTSTDL  Char 36$36.   $36.    XBTSTDL 
                             12XBTSTDLN Num   8BEST15.            XBTSTDLN
                              7XB_D     Num   8BEST14.            XB_D
                              8XB_M     Num   8BEST14.            XB_M
                              9XB_Y     Num   8BEST14.            XB_Y 
Super User
Posts: 19,878

Re: excel to sas

Posted in reply to rakeshvvv

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.

Ask a Question
Discussion stats
  • 5 replies
  • 337 views
  • 0 likes
  • 3 in conversation