DATA Step, Macro, Functions and more

Export to .csv value comes in a separate line

Reply
Regular Contributor
Posts: 215

Export to .csv value comes in a separate line

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   

 

Super User
Posts: 17,852

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

Check the file in a text editor not Excel. 

Regular Contributor
Posts: 215

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

[ Edited ]

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

PROC Star
Posts: 7,363

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

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

 

Super User
Posts: 10,514

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


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.

Regular Contributor
Posts: 215

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

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,
Valued Guide
Posts: 505

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

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




Super User
Posts: 10,514

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

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.

Ask a Question
Discussion stats
  • 7 replies
  • 179 views
  • 0 likes
  • 5 in conversation