DATA Step, Macro, Functions and more

problem with copy and past from excel

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

problem with copy and past from excel

I am creating a  data file and when I copy and paste data from excel I have problems , it puts a tab instead of empty spaces

data uno;
input a b c;
datalines;
4	6	8
1	6	8
4	5	7
;
run; 


I usually import but sometimes i need this kind of program


Accepted Solutions
Solution
‎08-09-2017 09:25 AM
Super User
Super User
Posts: 7,942

Re: problem with copy and past from excel

Yes, its another "feature" of Excel, when data is copied out it likes to split each cell, and tab is less likely to be in the data so it uses that.  One thing you can do if you have to use Excel and copy and paste, is to merge cells using Excel:

https://www.ablebits.com/office-addins-blog/2015/07/15/excel-concatenate-strings-cells-columns/

Then use the one merged cell to copy over.  However that really is just faffing about, either drop Excel totally, or just save to a CSV and read it in properly.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,913

Re: problem with copy and past from excel

Perhaps there is some translation difficulty, but this code works perfectly well on my computer. What exactly is the problem you are experiencing?

Occasional Contributor
Posts: 19

Re: problem with copy and past from excel

Posted in reply to PaigeMiller
It seems to be a problem with the data separation a tab instead of blank spaces
This is the log


data uno;
63 input a b c;
64 datalines;
NOTE: Invalid data for a in line 65 1-5.
NOTE: Invalid data for b in line 66 1-5.
NOTE: Invalid data for c in line 67 1-5.
REGLA: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

67 CHAR 4.5.7
ZONE 30303222222222222222222222222222222222222222222222222222222222222222222222222222
NUMR 49597000000000000000000000000000000000000000000000000000000000000000000000000000
NOTE: Invalid data errors for file CARDS occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
a=. b=. c=. _ERROR_=1 _N_=1
NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.UNO has 1 observations and 3 variables.
NOTE: Sentencia DATA used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

68 ;
69 run;
70
71 OPTIONS NOSYNTAXCHECK;
72 ODS HTML CLOSE;
73 &GRAPHTERM; ;*';*";*/;RUN;QUIT;
74 QUIT;RUN;
75 ODS HTML5 (ID=WEB) CLOSE;
76
77 ODS RTF (ID=WEB) CLOSE;
78 ODS PDF (ID=WEB) CLOSE;
NOTE: ODS PDF(WEB) printed no output.
(This sometimes results from failing to place a RUN statement before the ODS PDF(WEB) CLOSE statement.)
79 FILENAME _GSFNAME;
NOTE: Fileref _GSFNAME has been deassigned.
80 DATA _NULL_;
81 RUN;


Super User
Posts: 7,762

Re: problem with copy and past from excel

You need to tell SAS that it has to use a different delimiter:

data uno;
infile datalines dlm='09'x;
input a b c;
datalines;

If there's a possibilty of empty cells (two tabs in succession), also add the dsd option.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 19

Re: problem with copy and past from excel

Posted in reply to KurtBremser
Good!!! thanks
Solution
‎08-09-2017 09:25 AM
Super User
Super User
Posts: 7,942

Re: problem with copy and past from excel

Yes, its another "feature" of Excel, when data is copied out it likes to split each cell, and tab is less likely to be in the data so it uses that.  One thing you can do if you have to use Excel and copy and paste, is to merge cells using Excel:

https://www.ablebits.com/office-addins-blog/2015/07/15/excel-concatenate-strings-cells-columns/

Then use the one merged cell to copy over.  However that really is just faffing about, either drop Excel totally, or just save to a CSV and read it in properly.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 118 views
  • 1 like
  • 4 in conversation