topic Re: Proc export to TXT - dash problem in SAS Procedures

Proc export to TXT - dash problem

OrnellaNdongue — Mon, 29 May 2017 15:25:21 GMT

Hello,

I am trying to export the table "sas_table" to a txt file.

Here is my code :

filename export 'txt file name';

data _NULL_;

set sas_table;

file export lrecl=999999;

put @1 var1 $

@4 ";"

@5 var2 $

@6 ";"

@7 var3 $

@461 ";"

@462 var4 $

@494 ";"

;

'0D'x ;

run;

filename export;

When the value in the column var4 does not contain a dash, the semi-colon is at the position 494. But when the value contains a dash, the semi-colon is at the position 493. I want the semi-colon to always be at the position 494.

How can I fix this problem ? Thank you in advance for your help !

Re: Proc export to TXT - dash problem

Kurt_Bremser — Mon, 29 May 2017 15:29:48 GMT

Since you have a delimited output anyway, why don't you use

data _null_;
set sas_table;
file "output file" dlm=';' dsd lrecl=500 termstr=CRLF;
put
  var1
  var2
  var3
  var4
;
run;

Re: Proc export to TXT - dash problem

art297 — Mon, 29 May 2017 15:39:11 GMT

Before looking at whatever the problem might be, why does the following meaningless (by itself) statement follow the put statement?

'0D'x ;

Art, CEO, AnalystFinder.com

Re: Proc export to TXT - dash problem

OrnellaNdongue — Mon, 29 May 2017 15:52:36 GMT

Thank you for your answer.

The problem is that I need to have my data ranged in columns such as in the attachment.

With the solution you propose, I don't have the expected result.

Re: Proc export to TXT - dash problem

Tom — Mon, 29 May 2017 15:58:10 GMT

Since you are using pointer control there is no way that the semi-colon will NOT be at position 494.

If it looks like it is somewhere else then whatever tool you are using to view the file is doing some. Is it possible some of the values in your data are using UTF-8 codes that require multiple bytes per character?

Do you want a fixed column file? if so then why are you wasting space by writing the semi-colons?
If you want a delimited file when why do you care what column the data appears in rater then the relative field number when counting the delimiters?

Re: Proc export to TXT - dash problem

Tom — Mon, 29 May 2017 16:02:33 GMT

What sofware are you using to read the file? Why does it want all of the extra blanks if it also wants te semi-colons?

PS If you want to post a picture then click on the Insert Image icon (looks like a photo of the moon over a mountain range). If you want to post example text then then use the Insert code icon (looks like lower case letter i with curly brackets around it).

Re: Proc export to TXT - dash problem

art297 — Mon, 29 May 2017 16:14:14 GMT

The png you posted doesn't show the unexpected result you described.

Art, CEO, AnalystFinder.com

Re: Proc export to TXT - dash problem

Kurt_Bremser — Tue, 30 May 2017 07:27:58 GMT

@OrnellaNdongue wrote:

Thank you for your answer.

The problem is that I need to have my data ranged in columns such as in the attachment.

With the solution you propose, I don't have the expected result.

Does not compute. Delimiters are specifically used to enable variable-width columns and records. Using delimiters with fixed-width columns is a waste of space and brain-cycles, therefore stupid.

If you need fixed-width colums, do away with the delimiters. I can think of no software that would need both, so I smell a serious design error or misunderstanding here.

And the picture does not show any misplaced semicolon. I suggest posting the .txt file as attachment, and in which line the effect appears when you open the file; also post with which software you open the txt file. I also suspect that the problem lies there.

Alternatively use text editing software like notepad++ to view the file; there you can activate display in hex code, which will reveal the character sequence that leads your software astray.

Re: Proc export to TXT - dash problem

OrnellaNdongue — Tue, 30 May 2017 07:39:37 GMT

Thank you for your answer.

It's a business need. I will use the txt file for the update of an informatic system.

That's why I need a specific format with extra blanks and the semi-colons.

Here is a screen shot of the file I obtained :

Let me know if you need precisions.

Re: Proc export to TXT - dash problem

OrnellaNdongue — Tue, 30 May 2017 07:41:31 GMT

In fact... I put a screenshot of the problem in my precedent post.

Thank you in advance for your help !

Re: Proc export to TXT - dash problem

OrnellaNdongue — Tue, 30 May 2017 07:44:00 GMT

This statement just creates a line break. I tried to execute the code without it and the problem was still here.

Re: Proc export to TXT - dash problem

Kurt_Bremser — Tue, 30 May 2017 07:47:22 GMT

Screenshots just document the way the file is shown by your software. They do NOT document the actual format of the file.

Please post the the FILE as attachment, making sure that the erroneously displayed lines are included.

Re: Proc export to TXT - dash problem

OrnellaNdongue — Tue, 30 May 2017 07:49:36 GMT

Here is the file with the two columns concerned.

Re: Proc export to TXT - dash problem

OrnellaNdongue — Tue, 30 May 2017 07:57:47 GMT

I understand it doesn't make sense but I need both.

I use bloc-note to view the file. I will try an another tool to see if I get a different display.

Re: Proc export to TXT - dash problem

Kurt_Bremser — Tue, 30 May 2017 08:20:08 GMT

Inspect your data.

The value "SAINT-POL-SUR-MER" actually contains a CR/LF combination immediately following the "MER". This causes a premature linebreak.

Since the following semicolon would be at position 495 by my count, I suspect that you only had a UNIX linefeed (0A) there which was expanded to the DOS CRLF upon transfer from one platform to another.

You can search for '0d0a'x in the strings to remove those unexpected CRLFs.

Re: Proc export to TXT - dash problem

OrnellaNdongue — Tue, 30 May 2017 08:39:24 GMT

You were right.

I replaced the chain '0d0a'x by a blank and it works.

Thank your very much for your help !

Have a great day.

Re: Proc export to TXT - dash problem

Kurt_Bremser — Tue, 30 May 2017 08:50:29 GMT

@OrnellaNdongue wrote:

You were right.

I replaced the chain '0d0a'x by a blank and it works.

Thank your very much for your help !

Have a great day.

Maxim 3: Know your data.

This may involve using a $hex format to find weird character codes.

Re: Proc export to TXT - dash problem

OrnellaNdongue — Tue, 30 May 2017 09:25:50 GMT

Got it ! I will check my data first when I have this kind of problem.