SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

question in generating .txt file via File writer transformation.

Reply
Super Contributor
Posts: 625

question in generating .txt file via File writer transformation.

I'm unable to generate the proper text file which has logical record length over 256. I've the variable which has the starting position till 309. However if I tried to generate the text file which has logical record length as 310, I see only the variables which has logical record length till 256 is appearing correctly and other values of the variables are appearing in next line.

 

Appreciate if someone guide me to resolve this issue.

Valued Guide
Posts: 560

Re: question in generating .txt file via File writer transformation.

use LRECL=32767 for the file your writing, the default is 256. It is used to Specifies the default logical record length to use for reading and writing external files.

Thanks,
Suryakiran
Super Contributor
Posts: 625

Re: question in generating .txt file via File writer transformation.

Posted in reply to SuryaKiran

I tried this, but no luck.

Super User
Posts: 9,932

Re: question in generating .txt file via File writer transformation.

Post example data, and the code from the file writer node.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 625

Re: question in generating .txt file via File writer transformation.

Posted in reply to KurtBremser

This is the code which was being generated in File Writer Transformation. Screenshot of output file and File parameters are also given below. 

 

Example of Input Data:

 

INI INDTOP KHIREF PSI PUKDET GNKSEC AGI INKTYP INKRIC INKBOE PDK KNKVPB INKWYZ INKTKS INPPRD INDDBG INDDEI INDBOE INBNPR INBINK INBFRK INBBEK INBPOK INBTBD INBTOT INBINC INBANC NOSO
1,00346E+11 20180430 10000044 0 182 0 35712       3        0 21261 21292   0 0 0 0 0 0 0 0 0 1

 

 

%let SYSLAST = work.W70NABIY;

data _null_; 
   set &SYSLAST; 
      attrib INI length = $30; 
      attrib INDTOP length = 8
         format = 10.
         informat = 10.; 
      attrib KHIREF length = $20; 
      attrib PSI length = 8
         format = 9.
         informat = 9.; 
      attrib PUKDET length = $3; 
      attrib GNKSEC length = $9; 
      attrib AGI length = 6; 
      attrib INKTYP length = $1; 
      attrib INKRIC length = $1; 
      attrib INKBOE length = $1; 
      attrib PDK length = $2; 
      attrib KNKVPB length = $2; 
      attrib INKWYZ length = $1; 
      attrib INKTKS length = $1; 
      attrib INPPRD length = 4; 
      attrib INDDBG length = 8
         format = 10.
         informat = 10.; 
      attrib INDDEI length = 8
         format = 10.
         informat = 10.; 
      attrib INDBOE length = 8
         format = 10.
         informat = 10.; 
      attrib INBNPR length = 8
         format = 14.
         informat = 14.; 
      attrib INBINK length = 8
         format = 14.
         informat = 14.; 
      attrib INBFRK length = 8
         format = 14.
         informat = 14.; 
      attrib INBBEK length = 8
         format = 14.
         informat = 14.; 
      attrib INBPOK length = 8
         format = 14.
         informat = 14.; 
      attrib INBTBD length = 8
         format = 14.
         informat = 14.; 
      attrib INBTOT length = 8
         format = 14.
         informat = 14.; 
      attrib INBINC length = 8
         format = 14.
         informat = 14.; 
      attrib INBANC length = 8
         format = 14.
         informat = 14.; 
      attrib NOSO length = $1; 
      file '$FSAGDU/data/transport/out/qis_dwh/TBFR15_details.txt';
      
         put 
             @ 1 INI  
             @ 31 INDTOP 10. 
             @ 41 KHIREF  
             @ 61 PSI 9. 
             @ 70 PUKDET  
             @ 73 GNKSEC  
             @ 82 AGI  
             @ 88 INKTYP  
             @ 89 INKRIC  
             @ 90 INKBOE  
             @ 91 PDK  
             @ 93 KNKVPB  
             @ 95 INKWYZ  
             @ 96 INKTKS  
             @ 97 INPPRD  
             @ 101 INDDBG 10. 
             @ 111 INDDEI 10. 
             @ 121 INDBOE 10. 
             @ 131 INBNPR 14. 
             @ 145 INBINK 14. 
             @ 159 INBFRK 14. 
             @ 173 INBBEK 14. 
             @ 187 INBPOK 14. 
             @ 201 INBTBD 14. 
             @ 215 INBTOT 14. 
             @ 229 INBINC 14. 
             @ 243 INBANC 14. 
             @ 257 NOSO  
         ;
   
run; 

File parameters properties.PNG

 

Output File.PNG

Super User
Posts: 9,932

Re: question in generating .txt file via File writer transformation.

From what I see, you did everything right; IMO, the file statement in the data step should have the correct lrecl= option. Bring this to the attention of SAS Technical Support.

 

I also see some design issues with your data. INI is a number, but is stored in a string of length 30, wasting 22 bytes in storage, and probably some in output. INDTOP looks like a date, but is not stored as one. And there's probably more.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 4,698

Re: question in generating .txt file via File writer transformation.

[ Edited ]

@Babloo

Your SAS Metadata definitions for the External File look o.k. to me but it appears the LRECL=... code bit hasn't been generated even though you've defined it in Metadata. 

 

The Window you've posted looks different to my DIS version. Questions:

1. Which DIS version are you using?

2. Have you ensured that there is nowhere "user written" selected.

Super Contributor
Posts: 625

Re: question in generating .txt file via File writer transformation.

I'm using 4.21. I ensured user written code is not selected anywhere. Code which I given below is from File writer transformation where as I'm applying the lrecl option in metadata of the file where file writer transformation connects to. May be thats the reason for showing the lrecl option in file statement.

Respected Advisor
Posts: 4,698

Re: question in generating .txt file via File writer transformation.


@Babloo wrote:

I'm using 4.21. I ensured user written code is not selected anywhere. Code which I given below is from File writer transformation where as I'm applying the lrecl option in metadata of the file where file writer transformation connects to. May be thats the reason for showing the lrecl option in file statement.


The LRECL definition is part of the External File Metadata and not the File Writer so you're doing everything right. It looks to my as if your rather old version of DIS doesn't pick up this metadata setting when generating the code.

I suggest you contact SAS TechSupport with this one. May be there is a patch available.

 

Super User
Posts: 5,853

Re: question in generating .txt file via File writer transformation.

This has never worked, and this bug, sorry, lack of feature is still in 4.901.

Data never sleeps
Super User
Posts: 9,932

Re: question in generating .txt file via File writer transformation.


@LinusH wrote:

This has never worked, and this bug, sorry, lack of feature is still in 4.901.


Makes me think that almost nobody uses that interface for serious work, otherwise SAS TS would already have been swamped with tickets.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,441

Re: question in generating .txt file via File writer transformation.

It is the lrecl option:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003160472.htm

 

I do not know what File writer is or how to change options on it.

Ask a Question
Discussion stats
  • 11 replies
  • 214 views
  • 2 likes
  • 6 in conversation