Spaces added to between PIPE delimiter when writing to a flat file.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Spaces added to between PIPE delimiter when writing to a flat file.

Hello,

I have a SAS Job to write data from a table to a file, I have noticed that when column is empty in the table it writes one spaces to the file between PIPE.

Output file example:

1|105|ILB|Illinois Bell|0| | | |InActive| |BRITISH CONSULATE GENRL| | | | |P

Table format and Job are attached for ref.

SAS Job.JPGSASTable.JPG

Regards

Arun


Accepted Solutions
Solution
‎06-22-2013 11:14 PM
Respected Advisor
Posts: 3,887

Re: Spaces added to between PIPE delimiter when writing to a flat file.

Which DIS version are you using?

With DIS4.6 it's even worse as the FileWriter also adds quotes and I haven't found a way to suppress this. The FileWriter allows to add options to the File statement - eg. "dsd" - but this won't resolve the issue with the quotes.

You will get automatic code generated as in below example:

data _null_;
   set &SYSLAST;
      attrib Name length = $8;
      attrib Sex length = $1;
      attrib Age length = 8;
      attrib Height length = 8;
      attrib Weight length = 8;
      quote='"';
      file 'C:\_temp\class_txt.txt' dlm='|';
     
         put
            quote +(-1) Name +(-1) quote
            quote +(-1) Sex +(-1) quote
            quote +(-1) Age +(-1) quote
            quote +(-1) Height +(-1) quote
            quote +(-1) Weight +(-1) quote
            ;

run;

What you could do:

Set up your DIS job using the FileWriter, then go to the Code tab and change "Code generation mode" to "User written body". After that change your code as below.

options missing='';

data _null_;

   set &SYSLAST;

      attrib DMT_RECORD_NUM length = 8;

      attrib ECATS_CNTRCT_ID length = $128;

      attrib ECATS_LEGAL_ENTITY_CODE length = $128;

  attrib ECATS_LEGAL_ENTITY_DESCR length = $128;

      attrib AD_ID length = $128;

      attrib AD_NAME length = $128;

      attrib AD_REP_NAME length = $128;

      attrib DM_NAME length = $128;

      attrib STATUS_NAME length = $128;

      attrib OTHER_COMMITMENT length = $128;

      attrib CUST_LISTED_NAME length = $100;

      attrib CPE_DELIVERY_DT length = $128;

      attrib CPE_INSTALLATION_DT length = $128;

      attrib CPE_MAINT_END_DT length = $128;

      attrib CPE_MAINT_START_DT length = $128;

      attrib ECRM_CONTRACT_TYPE length = $128;

      attrib ECRM_CONTRACT_SUB_TYPE length = $128;

      file '/staging/econ/PD817_Project/Dev/OutputFiles/CDR_Files_06042013/att_imp_dmt_legal_entities_test.dat' dlm='|' DSD;

               put

            DMT_RECORD_NUM

            ECATS_CNTRCT_ID

            ECATS_LEGAL_ENTITY_CODE

            ECATS_LEGAL_ENTITY_DESCR

            AD_ID

            AD_NAME

            AD_REP_NAME

            DM_NAME

            STATUS_NAME

            OTHER_COMMITMENT

            CUST_LISTED_NAME

            CPE_DELIVERY_DT

            CPE_INSTALLATION_DT

            CPE_MAINT_END_DT

            CPE_MAINT_START_DT

            ECRM_CONTRACT_TYPE

            ECRM_CONTRACT_SUB_TYPE

            ;

  run;

View solution in original post


All Replies
Super User
Super User
Posts: 6,499

Re: Spaces added to between PIPE delimiter when writing to a flat file.

Is "File Writer" node one supplied by SAS or a code node with user written SAS code?

For either can you tell if it actually uses SAS to write the file? If so can you share the code and/or the log?

Occasional Contributor
Posts: 8

Re: Spaces added to between PIPE delimiter when writing to a flat file.

Tom,

They are SAS supplied transformation;

I have copied the code of File Writer below; I am not sure spaces are inserted to table in previous steps or not; but my requirement is to clear up before or during the write operation to File. So that when there is a empty field it should have two pipes without a space (||);

Couple of ways I tried;

Added a Data Validation node and tried Trim(field name) in the Expression;

Also Tried SubStr (1,Length(FieldName))

And Compress(fieldname)

All tried on Expression, but nothing worked.

------------------

File Writer Code:-

/*---- Map the columns  ----*/

proc datasets lib = work nolist nowarn memtype = (data view);

   delete W6AVNSSW;

quit;

%put %str(NOTE: Mapping columns ...);

proc sql;

   create view work.W6AVNSSW as

      select

         ROWNUM as DMT_RECORD_NUM  

            label = 'Derived by the DMT Tool',

         ECATS_CNTRCT_ID,

         ECATS_LEGAL_ENTITY_CODE,

         ECATS_LEGAL_ENTITY_DESCR,

         AD_ID,

         AD_NAME,

         AD_REP_NAME,

         DM_NAME,

         STATUS_NAME,

         OTHER_COMMITMENT,

         CUST_LISTED_NAME,

         CPE_DELIVERY_DT,

         CPE_INSTALLATION_DT,

         CPE_MAINT_END_DT,

         CPE_MAINT_START_DT,

         ECRM_CONTRACT_TYPE,

         ECRM_CONTRACT_SUB_TYPE

   from &SYSLAST

   ;

quit;

%let SYSLAST = work.W6AVNSSW;

data _null_;

   set &SYSLAST;

      attrib DMT_RECORD_NUM length = 8;

      attrib ECATS_CNTRCT_ID length = $128;

      attrib ECATS_LEGAL_ENTITY_CODE length = $128;

      attrib ECATS_LEGAL_ENTITY_DESCR length = $128;

      attrib AD_ID length = $128;

      attrib AD_NAME length = $128;

      attrib AD_REP_NAME length = $128;

      attrib DM_NAME length = $128;

      attrib STATUS_NAME length = $128;

      attrib OTHER_COMMITMENT length = $128;

      attrib CUST_LISTED_NAME length = $100;

      attrib CPE_DELIVERY_DT length = $128;

      attrib CPE_INSTALLATION_DT length = $128;

      attrib CPE_MAINT_END_DT length = $128;

      attrib CPE_MAINT_START_DT length = $128;

      attrib ECRM_CONTRACT_TYPE length = $128;

      attrib ECRM_CONTRACT_SUB_TYPE length = $128;

      file '/staging/econ/PD817_Project/Dev/OutputFiles/CDR_Files_06042013/att_imp_dmt_legal_entities_test.dat' dlm='|';

     

         put

            DMT_RECORD_NUM

            ECATS_CNTRCT_ID

            ECATS_LEGAL_ENTITY_CODE

            ECATS_LEGAL_ENTITY_DESCR

            AD_ID

            AD_NAME

            AD_REP_NAME

            DM_NAME

            STATUS_NAME

            OTHER_COMMITMENT

            CUST_LISTED_NAME

            CPE_DELIVERY_DT

            CPE_INSTALLATION_DT

            CPE_MAINT_END_DT

            CPE_MAINT_START_DT

            ECRM_CONTRACT_TYPE

            ECRM_CONTRACT_SUB_TYPE

            ;

run;

%rcSet(&syserr);

Super User
Super User
Posts: 6,499

Re: Spaces added to between PIPE delimiter when writing to a flat file.

Try adding the DSD option to the FILE statement in the generated DATA step.

Occasional Contributor
Posts: 8

Re: Spaces added to between PIPE delimiter when writing to a flat file.

Tom,

Can you pls give me more details, how to set DSD option; is this a property on the FileWriter Transformation?

On which tab I have to select it;

I am not an expert in SAS jobs, beginner :-)

Thanks

Arun

Super User
Posts: 9,676

Re: Spaces added to between PIPE delimiter when writing to a flat file.

file '/staging/econ/PD817_Project/Dev/OutputFiles/CDR_Files_06042013/att_imp_dmt_legal_entities_test.dat' dlm='|'  DSD ;

Solution
‎06-22-2013 11:14 PM
Respected Advisor
Posts: 3,887

Re: Spaces added to between PIPE delimiter when writing to a flat file.

Which DIS version are you using?

With DIS4.6 it's even worse as the FileWriter also adds quotes and I haven't found a way to suppress this. The FileWriter allows to add options to the File statement - eg. "dsd" - but this won't resolve the issue with the quotes.

You will get automatic code generated as in below example:

data _null_;
   set &SYSLAST;
      attrib Name length = $8;
      attrib Sex length = $1;
      attrib Age length = 8;
      attrib Height length = 8;
      attrib Weight length = 8;
      quote='"';
      file 'C:\_temp\class_txt.txt' dlm='|';
     
         put
            quote +(-1) Name +(-1) quote
            quote +(-1) Sex +(-1) quote
            quote +(-1) Age +(-1) quote
            quote +(-1) Height +(-1) quote
            quote +(-1) Weight +(-1) quote
            ;

run;

What you could do:

Set up your DIS job using the FileWriter, then go to the Code tab and change "Code generation mode" to "User written body". After that change your code as below.

options missing='';

data _null_;

   set &SYSLAST;

      attrib DMT_RECORD_NUM length = 8;

      attrib ECATS_CNTRCT_ID length = $128;

      attrib ECATS_LEGAL_ENTITY_CODE length = $128;

  attrib ECATS_LEGAL_ENTITY_DESCR length = $128;

      attrib AD_ID length = $128;

      attrib AD_NAME length = $128;

      attrib AD_REP_NAME length = $128;

      attrib DM_NAME length = $128;

      attrib STATUS_NAME length = $128;

      attrib OTHER_COMMITMENT length = $128;

      attrib CUST_LISTED_NAME length = $100;

      attrib CPE_DELIVERY_DT length = $128;

      attrib CPE_INSTALLATION_DT length = $128;

      attrib CPE_MAINT_END_DT length = $128;

      attrib CPE_MAINT_START_DT length = $128;

      attrib ECRM_CONTRACT_TYPE length = $128;

      attrib ECRM_CONTRACT_SUB_TYPE length = $128;

      file '/staging/econ/PD817_Project/Dev/OutputFiles/CDR_Files_06042013/att_imp_dmt_legal_entities_test.dat' dlm='|' DSD;

               put

            DMT_RECORD_NUM

            ECATS_CNTRCT_ID

            ECATS_LEGAL_ENTITY_CODE

            ECATS_LEGAL_ENTITY_DESCR

            AD_ID

            AD_NAME

            AD_REP_NAME

            DM_NAME

            STATUS_NAME

            OTHER_COMMITMENT

            CUST_LISTED_NAME

            CPE_DELIVERY_DT

            CPE_INSTALLATION_DT

            CPE_MAINT_END_DT

            CPE_MAINT_START_DT

            ECRM_CONTRACT_TYPE

            ECRM_CONTRACT_SUB_TYPE

            ;

  run;

Occasional Contributor
Posts: 8

Re: Spaces added to between PIPE delimiter when writing to a flat file.

Thanks a lot; it worked.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1268 views
  • 0 likes
  • 4 in conversation