BookmarkSubscribeRSS Feed
mlogan
Lapis Lazuli | Level 10

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   

 

7 REPLIES 7
Reeza
Super User

Check the file in a text editor not Excel. 

mlogan
Lapis Lazuli | Level 10

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

art297
Opal | Level 21

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

 

ballardw
Super User

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

mlogan
Lapis Lazuli | Level 10
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,
rogerjdeangelis
Barite | Level 11
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




ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 4298 views
  • 0 likes
  • 5 in conversation