topic Re: Export to .csv value comes in a separate line in SAS Programming

Export to .csv value comes in a separate line

mlogan — Mon, 10 Apr 2017 18:18:15 GMT

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

Re: Export to .csv value comes in a separate line

Reeza — Mon, 10 Apr 2017 18:20:29 GMT

Check the file in a text editor not Excel.

Re: Export to .csv value comes in a separate line

mlogan — Mon, 10 Apr 2017 18:45:20 GMT

Hi Reeza, Same result when I export this file to .txt format OR open the file with text editor.

Re: Export to .csv value comes in a separate line

art297 — Mon, 10 Apr 2017 19:00:47 GMT

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

Re: Export to .csv value comes in a separate line

ballardw — Mon, 10 Apr 2017 19:52:38 GMT

@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.

Re: Export to .csv value comes in a separate line

mlogan — Mon, 10 Apr 2017 20:00:17 GMT

Hi ballardw, You are right the excel I imported the file from had a carriage return on it's value and that is causing the problem. However if I export it back to excel it seem OK, but .csv or .txt doesn't seem OK. Thanks,

Re: Export to .csv value comes in a separate line

rogerjdeangelis — Mon, 10 Apr 2017 20:09:36 GMT

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

Re: Export to .csv value comes in a separate line

ballardw — Mon, 10 Apr 2017 21:08:59 GMT

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.