Hi All,
I have the following SAS data table.
ID State Comment Action ID
123 NY Good 458425
234 TX Moderate but 256532
Liveable
543 CA Not bad 526547
PROC EXPORT DATA=Have
OUTFILE="C:\Want.csv"
DBMS=csv
REPLACE;
RUN;
When I use the above code to export the table in .csv, I get and extra rows where the secord line of the comment field is thrown off to an extra row. Can someone please tell me how to fix it.
ID State Comment Action ID
123 NY Good 458425
234 TX Moderate but
Liveable 256532
543 CA Not bad 526547
Check the file in a text editor not Excel.
Hi Reeza, Same result when I export this file to .txt format OR open the file with text editor.
Can you attaachyour sas dataset here (you may have to zip it first .. I'm not sure)? That would definitely help.
Art, CEO, AnalystFinder.com
@mlogan wrote:
Hi Reeza, Same result when I export this file to .txt format OR open the file with text editor.
If you see this behavior and have not opened and saved in Excel it may mean that your comment text has a line feed, carriage return, both or other special characters embedded in the text.
Export to .csv value comes in a separate line
see
https://goo.gl/ZIZI9A
https://communities.sas.com/t5/Base-SAS-Programming/Export-to-csv-value-comes-in-a-separate-line/m-p/348823
I assume excel was the source not your SAS dataset?
It is always best to work with data in its original format.
You can translate the passthru query below to native SAS and use
your SAS daatset as input it should fix your 'alt-enters'.
You can solve this without the excel source but I thought I would
show users how to fix the issue on the excel side.
The SAS dataset probably came from excel?
You probably gave carriage-returns<alt-enter>, '0D'x, in your
data. It is best to fix this on the excel side.
I am going to assume you have the excel file;
HAVE
====
+--------------------------------------------------------------+
| A ] | B | C | D |
+--------------------------------------------------------------+
1 | ID | STATE | ACTION | COMMENT |
+------------+------------+------------+-----------------------+
2 | 123 | NY | 458425 | Good |
+------------+------------+------------+-----------------------+
3 | 234 | TX | 2565325 | Moderate<alt-enter> |
| | | | But<alt-enter> |
| | | | Liveable |
+------------+------------+------------+-----------------------+
4 | 543 | CA | 526547 | Not<alt-enter> |
| | | | Bad |
+------------+------------+------------+-----------------------+
As a side note here is what a the csv looks like with the alt enters.
COMMENT,ID,STATE,ACTIONG
ood,123,NY,458425
Moderate
but
Liveable",234,TX,256532
Not
bad",543,CA,526547
WANT SAS dataset with spaces for alt_enter
===========================================
Up to 40 obs WORK.WANT total obs=3
Obs ID STATE ACTION COMMENT_FIX
1 123 NY 458425 Good
2 234 TX 256532 Moderate but Liveable
3 543 CA 526547 Not bad
WORKING CODE
============
Passthru to excel (replace alt-enter with space
Replace(comment,chr(13),chr(32)) as comment_fix
* _ _ _
_ __ ___ __ _| | _____ _ __ _ __ ___ | |__ __ _| |_____ __
| '_ ` _ \ / _` | |/ / _ \_____| '_ \| '__/ _ \| '_ \ ____\ \/ / / __\ \/ /
| | | | | | (_| | < __/_____| |_) | | | (_) | |_) |_____> <| \__ \> <
|_| |_| |_|\__,_|_|\_\___| | .__/|_| \___/|_.__/ /_/\_\_|___/_/\_\
|_|
;
%utlfkil(d:/xls/have.xlsx);
libname xel "d:/xls/have.xlsx";
data xel.have;
informat comment $32.;
input id State$ Comment$ Action$ ;
comment=tranwrd(comment,'z','0D'x);
cards4;
123 NY Good 458425
234 TX ModeratezbutzLiveable 256532
543 CA Notzbad 526547
;;;;
run;quit;
libname xel clear;
* _ _ __ _
_ __ __ _ ___ ___| |_| |__ _ __ _ _ / _(_)_ __
| '_ \ / _` / __/ __| __| '_ \| '__| | | |_____| |_| \ \/ /
| |_) | (_| \__ \__ \ |_| | | | | | |_| |_____| _| |> <
| .__/ \__,_|___/___/\__|_| |_|_| \__,_| |_| |_/_/\_\
|_|
;
proc sql dquote=ansi;
connect to excel (Path="d:\xls\have.xlsx" mixed=yes);
create
table want as
select * from connection to Excel
( Select
id
,state
,action
,Replace(comment,chr(13),chr(32)) as comment_fix
from
[have$]
) ;
disconnect from Excel;
Quit;
2441 proc sql dquote=ansi;
2442 connect to excel (Path="d:\xls\have.xlsx" mixed=yes);
NOTE: Data source is connected in READ ONLY mode.
2443 create
2444 table want as
2445 select * from connection to Excel
2446 ( Select
2447 id
2448 ,state
2449 ,action
2450 ,Replace(comment,chr(13),chr(32)) as comment_fix
2451 from
2452 [have$]
2453 ) ;
NOTE: Table WORK."WANT" created, with 3 rows and 4 columns.
2454 disconnect from Excel;
2455 Quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
Excel is just "hiding" the character. If you need to use the file in another program that expects actual CSV then you'll need to find out which character(s) and remove it from the string before export.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.