DATA Step, Macro, Functions and more

Importing from Excel to SAS weird errors

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Importing from Excel to SAS weird errors

SAS gurus,

            This is following up with yesterday's discussion. I used proc import (or IMPORT WIZARD) and downloaded the data and extracted the time stamp. Everything looks good but I am getting weird errors. 

 

 1. The date time i am reading is coming out as 11JUL17:12:40:35  (numerical value)

but I want to get 7/11/2017 12:40:35 PM (character value).

 

2. when reading apostrophe (') or ampersand (&) in the character data, SAS is converting it as ' (for ') and & (for &). Is there a way to fix it?

 

3. How can I write input and infile statements and read this data in SAS. Is there a way that I can ask SAS to read data only as Character and later I can change to numeric whereever needed. 

 

Dummy data is below. 

Thanks,

 

 


Accepted Solutions
Solution
‎07-13-2017 12:29 PM
Super User
Posts: 10,466

Re: Importing from Excel to SAS weird errors


buddha_d wrote:

I agree, CSV is much better format. But I am doing it as my boss wants me to do it this way (importing directly from excel rather than saving it for as CSV file and then reading it). Thanks


Record the amount of time each change takes you for a short period of time, perhaps one or two weeks. Project that time time over a year. Apply your pay rate to the time. Use that information to lead into a discussion about overall cost of a process versus the one minute to do save as and change the input file name and output datset names if you are creating multiple data sets.

 

Add it some bits about the cost in what happens with variable type changes such as changing variables to match other data sets for combined analysis and reporting.

You may have a bit more success if approaching management with impact to bottom line information.

View solution in original post


All Replies
Super User
Super User
Posts: 6,498

Re: Importing from Excel to SAS weird errors

A SAS dataset as a nice clean rectangular structures, unlike Excel sheets which can have anything in any cell.

You can tell SAS to read only part of the data. 

The easiest option if the DATAROW option on PROC IMPORT.

So here is a method to read the TITLE line, HEADER row and DATA block as three different SAS datasets.

So first read the title. In this example the DATETIME seems to be in column I of the sheet.

proc import datafile="&path\DUMMY DATA.xlsx"
  dbms=xlsx
  out=title_row replace
;
  getnames=no;
  datarow=1;
run;
data title_row;
 set title_row(obs=1);
 call symputx('datetime',strip(substr(i,10)));
run;
%put &=datetime;

Then you can read in the variable names and convert them into a string of values that you could use in a RENAME statement.

proc import datafile="&path\DUMMY DATA.xlsx"
  dbms=xlsx
  out=varnames replace
;
  getnames=no;
  datarow=19;
run;
proc transpose data=varnames(obs=1) out=varnames ;
  var _all_;
run;
data _null_;
 set varnames end=eof;
 length renames $2000 ;
 retain renames ;
 renames=catx(' ',renames,catx('=',_name_,nliteral(translate(strip(col1),'__',' /'))));
 if eof then call symputx('renames',renames);
run;

Then finally you could read the data and assign the variable names you just calcuated using the RENAME= dataset option.

proc import datafile="&path\DUMMY DATA.xlsx"
  dbms=xlsx
  out=datablock (rename=(&renames))
  replace
;
  getnames=no;
  datarow=20;
run;

 

Contributor
Posts: 71

Re: Importing from Excel to SAS weird errors

Tom, By for you gave a nice code to import. Thanks for that. But I want SAS to read all the data as character. With your  code some fields are still reading as numberic. 

              Is there a way or code that makes SAS read all data as character data (all fields)??

 

thanks again

Super User
Super User
Posts: 6,498

Re: Importing from Excel to SAS weird errors

[ Edited ]

If you want the data to be treated as character then you should define it that way in the XLSX file.

 

You could try opening the spreadsheet using an ODBC connection to Excel and then you can use the SASDBTYPE= dataset option to tell SAS how to generate the SQL to query the data, but that is not something I ever do and it also probably requires that you are running SAS on a Windows machine (although I am not sure about that).

 

Although if your column headers are not numbers then you could read the headers as part of the data at the presence of the names should make SAS treat the whole column as characters. But that would still cause trouble for numbers with Excel formats attached to them (like dates).

Contributor
Posts: 71

Re: Importing from Excel to SAS weird errors

Thanks Tom for the reply. I don't know how to do in SQL Smiley Sad

But I will see if any one has anything else to add.

1. The dataset I get everyday varies no of rows , What is the best option to do, like datarow=20 and it will start from 20 and go until the end? 

2. Does the above statement applies to SAS EG as well? Coz I see different results from windows SAS to SAS EG

 

Thanks

 

Super User
Posts: 6,928

Re: Importing from Excel to SAS weird errors


buddha_d wrote:

Thanks Tom for the reply. I don't know how to do in SQL Smiley Sad

But I will see if any one has anything else to add.

1. The dataset I get everyday varies no of rows , What is the best option to do, like datarow=20 and it will start from 20 and go until the end? 

2. Does the above statement applies to SAS EG as well? Coz I see different results from windows SAS to SAS EG

 

Thanks

 


#1 the best option is to beat some sense into the idiot delivering data in changing formats. Set up a clear definition how the input data has to be structured and reject anything that doesnt't fit. This definition may include pointers to variable layouts, so you can set up your porcess to automatically adapt to the changing layouts.

But having to re-program an import step daily is incredibly stupid and a waste of time that would people get fired in my organisation. Or relegated to tasks that fit their mental capabilities, like watering the plants.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 103

Re: Importing from Excel to SAS weird errors

Hi,

 

Same code as yesterday with 'SHEET' instead of 'RANGE'.

This works for me:

 

PROC IMPORT OUT= WORK.tmp 
            DATAFILE= "C:\Temp\DUMMY DATA.xlsx" 
            DBMS=EXCEL REPLACE;
     SHEET="Accounting"; 
     GETNAMES=NO;
     MIXED=NO;
     SCANTEXT=NO;
     USEDATE=NO;
     SCANTIME=NO;
RUN;

Cheers

________________________

- That still only counts as one -

Contributor
Posts: 71

Re: Importing from Excel to SAS weird errors

Oligolas,This code works, but if you want to automate your system, you can't use proc import everyday. So any other way to specifically read data (may be infile input statements) and transform this data? 

 

Thanks for your reply

Contributor
Posts: 71

Re: Importing from Excel to SAS weird errors

KurtBremser, Thank you for such valuable comments. Yes, you are right, I want to write a program with pointers that would get the importing automated (rather than proc import). That is why I was asking about input and infile statements to program it. Could you please share some code to read the data and tips to automate it? 

                Your expertise is very much appreciated and please help me.

 

Thanks,

 

Super User
Posts: 6,928

Re: Importing from Excel to SAS weird errors

First, you have to have a solid agreement on the structure of the data you will import. This is independent from the file format.

Once you have the definitions in place (and are able to supply examples), then we can start to create valid code.

Until then, it would only be a waste of time, something I do only when getting paid for.

 

One of the major flaws of the Excel format for file transfer is the fact that one can only use methods in SAS that involve guessing (proc import or libname), and will therefore produce unpredictable results when the structure changes (without you as the user having any influence on it), which are hard to impossible to deal with in code.

With a proper file format like csv you can write a data step (that reads everything into char, so you can do conversions later, depending on certain markers) that won't do any changes on its own.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 71

Re: Importing from Excel to SAS weird errors

Yes Kurt, I agree with you. The realtime data has a fixed excel format, but if I use proc import it messing up with SAS (as it is guessing for numeric or character or date ). How to write code to have a fixed format? 

             If you know any papers or material to get me started that would be great help KurtBremser

Super User
Posts: 6,928

Re: Importing from Excel to SAS weird errors


buddha_d wrote:

Yes Kurt, I agree with you. The realtime data has a fixed excel format, but if I use proc import it messing up with SAS (as it is guessing for numeric or character or date ). How to write code to have a fixed format? 

             If you know any papers or material to get me started that would be great help KurtBremser


That is very simple. Save to csv from Excel, and write a data step for the resulting text file. You can't do that for Excel files, ergo: dump the crappy Excel format.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 71

Re: Importing from Excel to SAS weird errors

I agree, CSV is much better format. But I am doing it as my boss wants me to do it this way (importing directly from excel rather than saving it for as CSV file and then reading it). Thanks

Frequent Contributor
Posts: 103

Re: Importing from Excel to SAS weird errors

Well, if you are using a well define structure, you can absolutely use PROC IMPORT everyday. I provided you a piece of code to do so.

If not, you could tell SAS to use powershell to automatically convert your xlsx to csv before importing. I would recommend you to consult the appropriate forums.

 

I would recommend you to inform yourself extensively about what you are doing, the benefits and disadvantages and not to rely on a a piece of code some guy posted in here, me included. I consider this as part of your job. Your call.

 

But I think the best way to operate for big data would be to use an adequate data management system and not excel.

And sincerely if your boss is not open to the discussion, you should consider to quit, seriously.

 

________________________

- That still only counts as one -

Super User
Posts: 6,928

Re: Importing from Excel to SAS weird errors

Well said, @Oligolas!

I was already composing a message that contained several uses of the word "idiot" next to "boss".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 214 views
  • 4 likes
  • 5 in conversation