topic Re: problem with copy and past from excel in SAS Programming

problem with copy and past from excel

adricano — Wed, 09 Aug 2017 13:05:00 GMT

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

Re: problem with copy and past from excel

PaigeMiller — Wed, 09 Aug 2017 13:11:28 GMT

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

Re: problem with copy and past from excel

RW9 — Wed, 09 Aug 2017 13:18:33 GMT

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.

Re: problem with copy and past from excel

adricano — Wed, 09 Aug 2017 13:25:01 GMT

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;

Re: problem with copy and past from excel

Kurt_Bremser — Wed, 09 Aug 2017 13:27:35 GMT

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.

Re: problem with copy and past from excel

adricano — Wed, 09 Aug 2017 13:30:13 GMT

Good!!! thanks