topic Re: Carriage Returns - Need to be removed in SAS Programming

Carriage Returns - Need to be removed

TonyVanHorn — Wed, 04 Sep 2019 16:38:57 GMT

How does one leverage the goodness of the TERMSTR=CRLF option in the context of reading in a .csv file that resides on an SFTP server?

when I run the following code:

data DATA_IN;
infile sftpdir(myfile.csv)
termstr=crlf
truncover;
input row $32767.;
run;

it results in the following error:

27 termstr=crlf
_______
23
ERROR 23-2: Invalid option name TERMSTR.

Re: Carriage Returns - Need to be removed

Tom — Wed, 04 Sep 2019 16:46:06 GMT

Does code that work when the libref SFTPDIR is just pointing to a regular directory on your SAS server?

What version of SAS are your running?

Re: Carriage Returns - Need to be removed

TonyVanHorn — Wed, 04 Sep 2019 16:47:54 GMT

Yes - I can point to a non-SFTP directory and process the file in the desired manner. I am on SAS 9.4.

Re: Carriage Returns - Need to be removed

Panagiotis — Wed, 04 Sep 2019 17:42:50 GMT

Does the DATA step to read in the CSV file work without the TERMSTR option? I want to make sure that the file is at least being read in correctly since I don't have much experience with using the SFTP Access Method. The file should at least be read in, even thought it'll be a mess. If it doesn't work i'd take a look at the documentation to use the SFTP Access Method: https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lestmtsglobal&docsetTarget=p0xln1fiwsr340n1xxf4mkmfxp6f.htm&locale=en

Looking at the documentation here for TERMSTR option it mentions the different types. Not sure if that's an issue: http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#chifoptfmain.htm

Also, i'd try and download the file and open it in a text editor like Notepad ++. That's a nice way to see what the returns look like. I made a test file called test.csv and attached the CSV file. Here is the CSV file in notepad++. It allows me to see the returns:

I used the TERMSTR option and it read it in correctly. See sample code below:

********************************;
* READING EACH COLUMN WITH DSD *;
********************************;
/*Without termstr=crlf*/
data test1;	
	infile "S:\test.csv" dsd firstobs=2 missover;
	input Var1:8. Var2:$8.;
run;

/*With termstr=crlf*/
data test2;	
	infile "S:\test.csv" dsd firstobs=2 termstr=crlf missover;
	input Var1:8. Var2:$8.;
run;

**************************************;
* READING EVERYTHING INTO ONE COLUMN *;
**************************************;
/*Without termstr=crlf*/
data test3;	
	infile "S:\test.csv" firstobs=2 truncover;
	input row $300.;
run;

/*With termstr=crlf*/
data test4;	
	infile "S:\test.csv" firstobs=2 termstr=crlf truncover;
	input row $300.;
	var1=scan(row,1);
	var2=scan(row,2);
run;

Here are some other posts that might help:

- Peter

Re: Carriage Returns - Need to be removed

Panagiotis — Wed, 04 Sep 2019 17:44:52 GMT

Also if you could post a few lines of fake sample data in the format you have, that would allow us to test some code.

- Peter

Re: Carriage Returns - Need to be removed

Tom — Wed, 04 Sep 2019 17:52:34 GMT

@TonyVanHorn wrote:

Yes - I can point to a non-SFTP directory and process the file in the desired manner. I am on SAS 9.4.

In that case you will probably need to download the file first and then read it.

filename sftpdir ... ;
filename copy temp;

data _null_;
  infile sftpdir(myfile.csv) ;
  file copy ;
  input;
  put _infile_;
run;

data DATA_IN;
  infile COPY termstr=crlf truncover;
  input row $32767.;
run;

Re: Carriage Returns - Need to be removed

TonyVanHorn — Wed, 04 Sep 2019 19:56:30 GMT

@Tom & @Panagiotis - It *seems* like I do need to 1st copy over the file from SFTP to a local drive in order to be able to use the TERMSTR=CRLF option... However, contrary to my earlier statement of "Yes - I can point to a non-SFTP directory and process the file in the desired manner..." I find that results actually aren't processing in the manner I'd like yet.

The picture above, I think, conveys the challenge. I'd like to read the pipe delimited rows from this csv file into one column so that resulting value would look as follows:

a|b|c: d thru g|h

Instead, I am still getting:

using the following approach:

data test;
infile "C:\csv containing variable with carriage returns.csv" 
firstobs=2
termstr=crlf 
truncover;
input row $32767.;
run;

Re: Carriage Returns - Need to be removed

Tom — Wed, 04 Sep 2019 20:09:53 GMT

Why do you want to combine two rows into one?

Is the problem that someone has made a file that cannot be parsed by including <CR><LF> in the middle of value of one of the fields?

They didn't even include any quotes around the value like Excel would have done.

Assuming you don't have any | characters in the data just count the number of |'s you have read. You might want to replace those special characters with something else that will be easier to deal with.

data test;
 infile 'c:\downloads\test.csv' firstobs=2 ;
 length line $32767 ;
 do until(n_pipe>=3);
   input;
   line=catx('<cr>',line,_infile_);
   n_pipe=sum(n_pipe,countc(_infile_,'|'));
 end;
run;

Obs            line            n_pipe

 1     a|b|c:<cr>d thru g|h       3

Re: Carriage Returns - Need to be removed

TonyVanHorn — Wed, 04 Sep 2019 20:32:24 GMT

To your question "why do you want to combine two rows into one?" because it is a pipe-delimited file and yes - there is a comment field whose values can contain carriage returns... It's how the data is being offered for me to pick up. 🙂 Also, I can reference the sftpdir() instead of having to copy over the file! Thank you for helping me solve this!