Learning SAS? Welcome to the exclusive online community for all SAS learners.

reading data which started in excel

Reply
Valued Guide
Valued Guide
Posts: 684

reading data which started in excel

For decades I have copied data from Excel and pasted them directly into my SAS program file (for regular SAS). A toy example is below. These numbers were first entered into Excel and then copied. This works perfectly fine in regular SAS. However, the input statement will not work correctly in University Edition. The error message is given below the program. Presumably, the embedded tabs from Excel are causing problems. I know this because adding the following statement before INPUT

file datafiles dlm='09'x;

solves the problem. So, I don't have a problem reading the data. However, I would like to know why I need to use this statement in UE, and do not need the statement in regular SAS. In fact, if I use this statement in regular SAS, the input fails.

Thanks.  lvm

 

data a;
input x y $ z;
datalines;
1    a    10
2    a    20
3    b    0
4    b    5
;
run;
proc print data=a;
run;

 

NOTE: Invalid data for x in line 62 1-6.
NOTE: Invalid data for z in line 64 1-5.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
 
64 CHAR 3.b.0
ZONE 30603222222222222222222222222222222222222222222222222222222222222222222222222222
NUMR 39290000000000000000000000000000000000000000000000000000000000000000000000000000
NOTE: Invalid data errors for file CARDS occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
x=. y=2a20 z=. _ERROR_=1 _N_=1
NOTE: Invalid data for x in line 65 1-5.
NOTE: LOST CARD.
66 ;
NOTE: Invalid data errors for file CARDS occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
x=. y= z=. _ERROR_=1 _N_=2
 
Super User
Posts: 19,817

Re: reading data which started in excel

My guess is it happens because you're most likely moving data from Windows to Unix, SAS UE is Unix
Super User
Super User
Posts: 7,970

Re: reading data which started in excel

It is as Reeza states, the two are running on different operating systems and interpreting hidden characters differently.  You have hit a "feature" of using Excel.  My suggestion is don't, Excel is not a data entry, data capture, database, data transfer or any other type of tool.  If you do have to enter values into Excel, then save the file as CSV and read that in with a SAS datastep + infile statement.

Valued Guide
Valued Guide
Posts: 684

Re: reading data which started in excel

All good points. I actually don't use Excel for anything. But almost everyone who brings me data uses Excel, so I am stuck with it. Interesting, when I took my toy example and copied it out of my original post, the embedded special characters were stripped away. Reading it in UE worked fine! I also discovered if I copy from Excel and paste into Microsoft Notepad, and then copy and paste into UE, everything works fine. I guess Notepad strips away the special characters also. This may help others who run into the same thing.
Super User
Super User
Posts: 7,970

Re: reading data which started in excel

You are not stuck with it.  This is a misconception drawn up on because of lack of documentation.  If I am to receive data the first thing I do is draw up a data transfer agreement.  This will detail the strcuture and various other things around the transfer of data.  At that point the discussion can be had as to what type the data will be stored at.  If a vendor insists that Excel is the only tool they have available (or due to sever lack of understanding of the damage Excel can do to your process or data), then instruct them to save the Excel file as a CSV file before they send it to you.  Why does this help you, well Excel has some nice "functionality" which will hide certain data artifacts, e.g. each cell has its own format so there can be mixes of character and numeric which can be hidden by making all cells character.  Other things such as precision can be hidden.  If they are the ones that save the CSV, then you bear no responsibility for incorrect data.  Personally I find this defensive transfer far better than reactive as everything is agreed up front, programmed once and problems only occur in the data sent, i.e. its the sender's problem, not yours - for instance if another column appears or data type changes, then you have an agreed document which they are not following.

Super User
Posts: 19,817

Re: reading data which started in excel

I think that depends on your role. As an analyst many don't have vendor agreements or the power to say no. Those who do say no stay where they are, those who get things done using Excel move forward....
Ask a Question
Discussion stats
  • 5 replies
  • 453 views
  • 2 likes
  • 3 in conversation