Re: How to get rid of the carriage returns from csv file

sruthim · Posted 08-15-2019 01:10 PM

the data in my output is going to the next line and the layout is all messed up. i tried different ways to remove the carraige returns but not successful. talking about columns - objectives and notes. could someone please help me?

Thank you!

CallID

CallType

CallDate

CallStatus

Objectives

Notes

SOFDocument

UserType

SDSRepID

EmployeeID

SalesRepLastName

SalesRepFirstName

SalesRepMiddleName

15356

Detail Only

########

Complete

Objectives:

Axumin - Prostate NEXT STEPS:

Notes:

Axumin - Prostate MESSAGES:

Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: No

SalesRep

196

2879142

Moody

Meredith

15358

Detail Only

########

Complete

Objectives:

Axumin - Prostate NEXT STEPS:

Notes:

Axumin - Prostate MESSAGES:

Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: No

SalesRep

196

2879142

Moody

Meredith

Reeza · Posted 08-15-2019 01:18 PM

See this thread:

https://communities.sas.com/t5/New-SAS-User/Reading-CSV-file-with-double-quotes-line-breaks-and-spac...

@sruthim wrote:

the data in my output is going to the next line and the layout is all messed up. i tried different ways to remove the carraige returns but not successful. talking about columns - objectives and notes. could someone please help me?

Thank you!

CallID CallType CallDate CallStatus Objectives Notes SOFDocument UserType SDSRepID EmployeeID SalesRepLastName SalesRepFirstName SalesRepMiddleName

15356 Detail Only ######## Complete Objectives:

Axumin - Prostate NEXT STEPS:

Notes:

Axumin - Prostate MESSAGES:

Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: No

SalesRep 196 2879142 Moody Meredith

15358 Detail Only ######## Complete Objectives:

Axumin - Prostate NEXT STEPS: Notes:

Axumin - Prostate MESSAGES:

Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: No

SalesRep 196 2879142 Moody Meredith

sruthim · Posted 08-15-2019 01:42 PM

Thank you @Reeza

Tom · Posted 08-15-2019 01:20 PM

Can you show us what the CSV file actually looks like? You appear to have copied something from a spreadsheet program and pasted it into the forum because it ended up looking like an HTML table instead of lines of text.

Use the Insert Code icon (looks like {i} on the menu bar) to get a pop-up window where you can paste example lines from your CSV file. Make sure to open the CSV file using a text editor and not a spreadsheet so you can see the actual lines of text. If you cannot figure that out just ask SAS to show you want is in the file.

data _null_;
  infile 'name of my csv file' obs=5;
  input;
  list;
run;

sruthim · Posted 08-15-2019 01:32 PM

"CallID","CallType","CallDate","CallStatus","Objectives","Notes","SOFDocument","UserType","SDSRepID","EmployeeID","SalesRepLastName","SalesRepFirstName","SalesRepMiddleName","SalesRepFullName","SalesRepAddressType","SalesRepAddress1","SalesRepAddress2","SalesRepAddress3","SalesRepCity","SalesRepState","SalesRepPostalCode","SalesRepPhone","SalesRepPhoneExt","SalesRepFax","SalesRepEmail","TerritoryLevel1Description","TerritoryLevel2Description","TerritoryLevel3Description","TerritoryLevel4Description","TerritoryLevel5Description","TerritoryLevel6Description","TerritoryLevel1","TerritoryLevel2","TerritoryLevel3","TerritoryLevel4","TerritoryDesc","SDSTerritoryID","TerritoryCode","TerritoryType","SDSCallEntityID","SDSCustomerID","CustomerID1","CustomerID2","CustomerID3","CustomerID4","CustomerID5","CustomerLastName","CustomerFirstName","CustomerMiddleName","CustomerFullName","Level","SDSLevelID","CustomerDegree","CustomerSpecialty","MedExam","SDSAddressID","CustomerAddressID","Address1","Address2","Address3","City","State","PostalCode","Phone","PhoneExt","Fax","SDSProductID","SDSStrengthID","ClientProductID1","ClientProductID2","ClientProductID3","ClientProductID4","ClientProductID5","FullProductDescription","ProductDescription","StrengthDescription","ProductManufacturer","SDSProductLotID","LotNumber","ExpireDate","Qty","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","CreateDate","CallDataSource"
"15356","Detail Only","7/23/2019 9:00:00 AM","Complete","Objectives: 

Axumin - Prostate NEXT STEPS:
","Notes: 

Axumin - Prostate MESSAGES:
	Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: No

","","SalesRep","196","2879142","Moody","Meredith"," ","Moody, Meredith","Primary Address","1738 Haight St. #208","","","San Francisco","CA","94117","415-307-0814","","","M.Moody@blueearthdx.com","US (SalesForce)","WEST","Northern California","","","","1026","1018","1022","","Northern California","1022","WE01004","Territory","16125","122867","1194883769","","","","","LEE","CHI","KEUNG","LEE, CHI KEUNG","Unassigned Decile","11","MD","","","41787","","2999 REGENT ST","#612","","BERKELEY","CA","94705","","","","","","","","","","","","","","","","","","","Axumin - Prostate","","","","","","","","","","7/25/2019 10:39:14 PM","App"
"15358","Detail Only","7/23/2019 9:30:00 AM","Complete","Objectives: 

Axumin - Prostate NEXT STEPS:
","Notes: 

Axumin - Prostate MESSAGES:
	Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: No

","","SalesRep","196","2879142","Moody","Meredith"," ","Moody, Meredith","Primary Address","1738 Haight St. #208","","","San Francisco","CA","94117","415-307-0814","","","M.Moody@blueearthdx.com","US (SalesForce)","WEST","Northern California","","","","1026","1018","1022","","Northern California","1022","WE01004","Territory","16127","122869","1184910044","","","","","OBERLIN","DANIEL","THOMAS","OBERLIN, DANIEL THOMAS","Unassigned Decile","11","MD","","","41789","","1441 EASTLAKE AVE STE 7416","","","LOS ANGELES","CA","90089","","","","","","","","","","","","","","","","","","","Axumin - Prostate","","","","","","","","","","7/25/2019 10:42:26 PM","App"
"15546","Detail Only","7/23/2019 9:30:00 AM","Complete","Objectives:

Hi Tom,

i opened it with Notepad and here it is.

Tom · Posted 08-15-2019 01:34 PM

Looks like you have quotes around the line breaks. So the simple quote counting method in the previously linked question should work for this file. Provided none of the the lines are longer than 32K bytes.

sruthim · Posted 08-15-2019 01:37 PM

i am sorry i didnt get you. waht previously linked question?

Tom · Posted 08-15-2019 01:40 PM

@Reeza already provided you a link to another question about this same issue on this forum that has the solution.

sruthim · Posted 08-15-2019 01:41 PM

This is the code i am using.

%let date = 20190731;
%let filename = Calls_X8668_20190731_cr;
%let filepath = "C:\Data\Sruthi\Synergistix\&filename..csv";
DATA &filename;
INFILE &filepath lrecl=10000 DLM = ',' DSD truncover firstobs = 2 TERMSTR=CRLF;
INPUT
CallID :$60.
CallType:$60.
CallDate:$60.
CallStatus:$60.
Objectives:$600.
CreateDate:$60.
CallDataSource:$60.;
/*Objectives=tranwrd(Objectives,'0D0A'x, '');
notes=tranwrd(notes,'0D0A'x, '');*/
run;

sruthim · Posted 08-15-2019 01:42 PM

okay. looking into it. was hoping to see an easier approach. but will try that. thank you!

Tom · Posted 09-09-2019 02:02 PM

@sruthim wrote:

okay. looking into it. was hoping to see an easier approach. but will try that. thank you!

Did you try the method in the other post? Did it work?

If not

did it fail in the step to create a cleaned up version of the text file? If so show the LOG from that step.

If not

did in fail in your step to read the new cleaned up version of the text file? Is so then show the LOG from that step.

sruthim · Posted 09-09-2019 02:20 PM

Hi Tom,

Yes, i tried the method you said. i was on a business trip and i didnt get
a chance to get back to you. trying to find my version for that code.
meanwhile this is the codei last ran. please find the screenshots of log as
well.
Thank you,
Sruthi

data _null_;
file "C:\Data\Sruthi\Synergistix\sample.csv";
put '"CallID","CallTyp","CallDate,"CallStatus"'
'0D'x
'",'
'"Objectives","Notes"'
'0A'x
'",'
'"SOFDocument","UserType"'
;
run;

data out;
INFILE 'C:\Data\Sruthi\Synergistix\sample.csv' dsd truncover firstobs=2;
length
CallID $60
CallType $60
CallDate $60
CallStatus $60
Objectives $600
Notes $600
SOFDocument $60
UserType $60;
INPUT
CallID :$60.
CallType:$60.
CallDate:$60.
CallStatus:$60.
Objectives:$600.
Notes:$600.
SOFDocument:$60.
UserType:$60.;
run;

proc print;
title 'File is read incorrectly due to embedded CR/LF';
run;

%let repA='@'; /* replacement character LF */
%let repD='$'; /* replacement character CR */

%let dsnnme='C:\Data\Sruthi\Synergistix\sample.csv'; /* use full path
of CSV file */

data _null_;
infile &dsnnme recfm=n sharebuffers;
file &dsnnme recfm=n;
retain open 0;
input
Objectives $char1.;
if Objectives = '"' then open = ^(open);

if open then do;
if Objectives = '0D'x then put &repD;
else if Objectives = '0A'x then put &repA;
end;
run;

data output;
infile 'C:\Data\Sruthi\Synergistix\sample.csv' dsd dlm =',' truncover;
input
CallID :$600.
CallType:$600.
CallDate:$600.
CallStatus:$600.
Objectives:$600.
Notes:$600.
SOFDocument:$600.
UserType:$600.;
run;

proc print;
title1 'File is read correctly after transformation ';
title2 'Look for printable characters &repa &repd in variable values ';
run;

data _null_; /* optional delete external file*/
fname="tempfile";
rc=filename(fname,"C:\Data\Sruthi\Synergistix\sample.csv");
if rc = 0 and fexist(fname) then rc=fdelete(fname);
rc=filename(fname);
run;

LOG

1848 file "C:\Data\Sruthi\Synergistix\sample.csv";
1849 put '"CallID","CallTyp","CallDate,"CallStatus"'
1850 '0D'x
1851 '",'
1852 '"Objectives","Notes"'
1853 '0A'x
1854 '",'
1855 '"SOFDocument","UserType"'
1856 ;
1857 run;

NOTE: The file "C:\Data\Sruthi\Synergistix\sample.csv" is:
Filename=C:\Data\Sruthi\Synergistix\sample.csv,
RECFM=V,LRECL=32767,File Size (bytes)=0,
Last Modified=09Sep2019:14:15:41,
Create Time=09Sep2019:14:14:40

NOTE: 1 record was written to the file
"C:\Data\Sruthi\Synergistix\sample.csv".
The minimum record length was 91.
The maximum record length was 91.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

1858 data out;
1859 INFILE 'C:\Data\Sruthi\Synergistix\sample.csv' dsd truncover
firstobs=2;
1860 length
1861 CallID $60
1862 CallType $60
1863 CallDate $60
1864 CallStatus $60
1865 Objectives $600
1866 Notes $600
1867 SOFDocument $60
1868 UserType $60;
1869 INPUT
1870 CallID :$60.
1871 CallType:$60.
1872 CallDate:$60.
1873 CallStatus:$60.
1874 Objectives:$600.
1875 Notes:$600.
1876 SOFDocument:$60.
1877 UserType:$60.;
1878 run;

NOTE: The infile 'C:\Data\Sruthi\Synergistix\sample.csv' is:
Filename=C:\Data\Sruthi\Synergistix\sample.csv,
RECFM=V,LRECL=32767,File Size (bytes)=93,
Last Modified=09Sep2019:14:15:41,
Create Time=09Sep2019:14:14:40

NOTE: 1 record was read from the infile
'C:\Data\Sruthi\Synergistix\sample.csv'.
The minimum record length was 26.
The maximum record length was 26.
NOTE: The data set WORK.OUT has 1 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.03 seconds

1879 proc print;
1880 title 'File is read incorrectly due to embedded CR/LF';
1881 run;

NOTE: There were 1 observations read from the data set WORK.OUT.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.37 seconds
cpu time 0.07 seconds

1882
1883 %let repA='@'; /* replacement character LF */
1884 %let repD='$'; /* replacement character CR */
1885
1886 %let dsnnme='C:\Data\Sruthi\Synergistix\sample.csv'; /* use full
path of CSV file */
1887
1888 data _null_;
SYMBOLGEN: Macro variable DSNNME resolves to
'C:\Data\Sruthi\Synergistix\sample.csv'
1889 infile &dsnnme recfm=n sharebuffers;
SYMBOLGEN: Macro variable DSNNME resolves to
'C:\Data\Sruthi\Synergistix\sample.csv'
1890 file &dsnnme recfm=n;
1891 retain open 0;
1892 input
1893 Objectives $char1.;
1894 if Objectives = '"' then open = ^(open);
1895 if open then do;
1896 if Objectives = '0D'x then
SYMBOLGEN: Macro variable REPD resolves to '$'
1896! put &repD;
1897 else if Objectives = '0A'x then
SYMBOLGEN: Macro variable REPA resolves to '@'
1897! put &repA;
1898 end;
1899 run;

NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The file/infile 'C:\Data\Sruthi\Synergistix\sample.csv' is:
Filename=C:\Data\Sruthi\Synergistix\sample.csv,
RECFM=N,LRECL=256,File Size (bytes)=93,
Last Modified=09Sep2019:14:15:41,
Create Time=09Sep2019:14:14:40

NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds

1900 data output;
1901 infile 'C:\Data\Sruthi\Synergistix\sample.csv' dsd dlm =','
truncover;
1902 input
1903 CallID :$600.
1904 CallType:$600.
1905 CallDate:$600.
1906 CallStatus:$600.
1907 Objectives:$600.
1908 Notes:$600.
1909 SOFDocument:$600.
1910 UserType:$600.;
1911 run;

NOTE: The infile 'C:\Data\Sruthi\Synergistix\sample.csv' is:
Filename=C:\Data\Sruthi\Synergistix\sample.csv,
RECFM=V,LRECL=32767,File Size (bytes)=93,
Last Modified=09Sep2019:14:15:58,
Create Time=09Sep2019:14:14:40

NOTE: 2 records were read from the infile
'C:\Data\Sruthi\Synergistix\sample.csv'.
The minimum record length was 28.
The maximum record length was 64.
NOTE: The data set WORK.OUTPUT has 2 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

1912
1913 proc print;
1914 title1 'File is read correctly after transformation ';
1915 title2 'Look for printable characters &repa &repd in variable
values ';
1916 run;

NOTE: There were 2 observations read from the data set WORK.OUTPUT.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

1917
1918 data _null_; /* optional delete external file*/
1919 fname="tempfile";
1920 rc=filename(fname,"C:\Data\Sruthi\Synergistix\sample.csv");
1921 if rc = 0 and fexist(fname) then rc=fdelete(fname);
1922 rc=filename(fname);
1923 run;

NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds

Tom · Posted 09-09-2019 04:28 PM

The logic of this step seems be to throw away everything except the embedded CR and LF's.

data _null_;
infile &dsnnme recfm=n sharebuffers;
file &dsnnme recfm=n;
retain open 0;
input
Objectives $char1.;
if Objectives = '"' then open = ^(open);

if open then do;
if Objectives = '0D'x then put &repD;
else if Objectives = '0A'x then put &repA;
end;
run;

Did you mean to do something like this instead?

data _null_;
  infile &dsnnme recfm=n sharebuffers;
  file &dsnnme recfm=n;
  retain open 0;
  input Objectives $char1.;
  if Objectives = '"' then open = ^(open);
  if open then do;
    if Objectives = '0D'x then put &repD;
    else if Objectives = '0A'x then put &repA;
    else put Objectives $char1. ;
  end;
  else put Objectives $char1.;
run;

Ksharp · Posted 08-16-2019 08:48 AM

Here is an example. Good Luck.


options compress=yes;
DATA have;
INFILE 'c:\temp\x.txt' lrecl=10000  DSD truncover firstobs = 2 TERMSTR=CRLF length=len;
input x $varying10000. len;
if not missing(x);
run;

data temp;
 set have;
 if prxmatch('/^"\d{5,}"/',x) then group+1;
run;

data temp1;
length have $ 10000;
 do until(last.group);
   set temp;
   by group;
   have=cats(have,x);
 end;
 drop x;
run;

data want;
 set temp1;
 array x{*} $ 400 CallID CallType CallDate CallStatus Objectives Notes SOFDocument UserType;
 do i=1 to dim(x);
   x{i}=dequote(scan(have,i,',','qm'));
 end;
 drop i;
 run;

sruthim · Posted 09-09-2019 01:53 PM

Hi ksharp,
this didnt work either 😞

SAS Innovate 2025: Call for Content