Import excel file

Reply
Contributor
Posts: 62

Import excel file

Hi ,

I need to import excel file. i use this code:

proc import datafile="D:...." out=try

                                         dbms=xls replace;

                                         sheet="sheet3";

                                         getbames=yes;

run;

but my variables names (first line in excel) is dates (12-Nov-2013, 13-Nov-2013, 16-No-2013 to 18-Nov-2013) and I want that the variable names would be like this, but SAS just leave only 16-No-2013 to 18-Nov-2013 and it does not take single dates

How could i fix it?

Thanks

Super User
Super User
Posts: 7,711

Re: Import excel file

Hi,

Firstly, you do not want variable names to be dates.   This just causes you a lot of extra work (i.e. you can't use arrays and such like easily).  Secondly, SAS will not like a number as first character, nor will it allow -, unless you you have forced it too, and it wouldn't be recommended.  Thirdly, as I always do, I recommend not using Excel or Proc Import.  Convert your Excel file to CSV, and write a datastep import.  This gives you, the person who knows the data and how to deal with it, the control over how SAS handles the data rather than SAS guessing what to do.

How does your spreadsheet look?  Maybe post some of it.  If the heading of a column is really "16-No-2013 to 18-Nov-2013" then you have all kinds of problems.  This is caused by Excel and people using that software.  All databases or software which deal with data have a fixed structure, i.e. columns have on format, one title.  Excel does not have that, you can do whatever you like where-ever you like.  If it was me I would send the file back and ask the vendor to supply a proper data transfer format file - CSV, XML etc.  If you can't then somehow you will have to modify the file yourself.

Ask a Question
Discussion stats
  • 1 reply
  • 164 views
  • 0 likes
  • 2 in conversation