Import data from excel file

Reply
Occasional Contributor
Posts: 5

Import data from excel file

Hi,

I tried to import the attachment file with import procedure from menu "file". This is the code:

PROC IMPORT OUT= WORK.a01

            DATAFILE= "C:\Users\Data.xls"

            DBMS=EXCELCS REPLACE;

     RANGE="Data$";

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

The text in the column F8 in the original file is in 7 different rows but the same text is in one row in the sas dataset. That is "normal", but the problem is that I can not identify the original rows because there is not any symbol (or something else) that can help me to identify the end (or the beginning) of the original rows.

This is a small file and I can put (by hand) some special characters, but if I have this situation in an excel file with many many rows.. this is a tragedy!

Could you help me?

Thank you.

Super User
Super User
Posts: 7,980

Re: Import data from excel file

Hi,

Yep, standard issues with using Excel I am afraid - Excel is not a tool to be used for anything other than spreadsheeting, and lots of these problems come up from using that software.  You can post process the imported data using the below, however I would suggest the use of anything else is better than Excel, get a database, or a form, CSV etc.

PROC IMPORT OUT= WORK.a01

            DATAFILE= "s:\temp\rob\Data.xls"

            DBMS=EXCEL REPLACE;

RANGE="Data$";

RUN;

data want;

  set a01;

  array f8_split{10} $200.;

  do i=1 to 10;

    f8_split{i}=scan(f8,i,'0A'x);

  end;

run;

Occasional Contributor
Posts: 5

Re: Import data from excel file

Hi,

thank you for your quickly reply. I know it would be better to use something different from xls, but that is what I received (..).

I was hoping not to have to use arrays (and then put the 10 different variables into single one..), but maybe this is the only one possibility.

Thank you again.

Super User
Super User
Posts: 7,980

Re: Import data from excel file

You could save the Excel file to CSV and write a datastep to read the file in (or a text replace of carriage returns with commas).  Yes, I am afraid we all face the lets put everything in Excel syndrome.

You don't need to use arrays, you could split and output a new row for each result (and if necessary transpose up).

Occasional Contributor
Posts: 5

Re: Import data from excel file

I will try..

Perfect, thank you! Smiley Wink

Ask a Question
Discussion stats
  • 4 replies
  • 276 views
  • 3 likes
  • 2 in conversation