topic Re: removing spaces in the blank fields in SAS Programming

removing spaces in the blank fields

niespolo — Tue, 08 Nov 2016 14:06:10 GMT

Hi,

I' would write in a text file some fields read from an Oracle table. Some numeric and character fileds could be null in the source table.
I would write an empty value "" in the file for each null field in input. I have changed the missing value as follows:

options missing = '';

But SAS converts null in " " for the character and the number fields.

Is it possible avoid the space " " when a value is missing and to write "" in the file ?

Many thanks.

Re: removing spaces in the blank fields

RW9 — Tue, 08 Nov 2016 14:29:07 GMT

What output file are you creating, how is the file created? Options missing only changes the SAS representation of a missing value.

Re: removing spaces in the blank fields

niespolo — Tue, 08 Nov 2016 14:36:33 GMT

The file is a .txt and has been created manually in a Windows folder and it's populated by a File Writer transformation by using SAS Data Integration Studio.

Re: removing spaces in the blank fields

Tom — Tue, 08 Nov 2016 14:41:40 GMT

You can avoid writing the anything for null values when using the DSD option on the FILE statement.

72   data _null_;
73      file log dsd ;
74      set sashelp.class (obs=3);
75      if _n_=2 then age=.;
76      if _n_=3 then sex=' ';
77      put (_all_) (+0);
78   run;

Alfred,M,14,69,112.5
Alice,F,,56.5,84
Barbara,,13,65.3,98

Re: removing spaces in the blank fields

niespolo — Tue, 08 Nov 2016 15:08:38 GMT

Hi Tom,

unfortunately I've never used the code behind the SAS Data Integration Studio transformation. So I don't understand how to apply your example in my project.

I've seen the code of the File writer transformation and it writes in the file by using a view. Attached is the code of the transformation.

Please, can you tell me how to add the DSD option in my code for resolve my problem?

Many thanks.

Re: removing spaces in the blank fields

Tom — Tue, 08 Nov 2016 15:16:12 GMT

That is some pretty ugly SAS code.

There isn't a DSD option in the GUI tool that you used to generate that code?

It seems strange that it would generate a delimited file and NOT include the option to use the DSD option.

Re: removing spaces in the blank fields

niespolo — Tue, 08 Nov 2016 16:25:02 GMT

It's possible to add some options to the INFILE statement by the GUI.
Attached is the INFILE statement that I'm using and the GUI for adding options.
Do you know if there is a correct option I can add for resolve my problem?

Many thanks.

Re: removing spaces in the blank fields

Tom — Tue, 08 Nov 2016 16:36:53 GMT

There is a DSD option in that picture. It is labeled:

Treat consecutive delimiters as a single delimiter.

From the wording it looks like to me that leaving it unchecked should cause the DSD option to be used.

But that screen shot looks like the settings for READING a file, not WRITING a file. An INFILE statement is used to READ from a file. A FILE statement would be used to WRITE to a file.

Re: removing spaces in the blank fields

niespolo — Tue, 08 Nov 2016 16:59:39 GMT

Unchecking the option "Treat consecutive delimiters as a single delimiter" removes the dtd option in the INFLILE statement, but the output in the file is removing the delimeters " and ". The spaces are not removed in the missing values.
The tool automatically generates the source code for writing in the file and it seems the FILE statement is not used (if you see the previous code I've attached). I could edit the code but don't know how to write "" in the file for the missing values, instead of " ". Is there a way to do this?

Re: removing spaces in the blank fields

Tom — Wed, 09 Nov 2016 15:40:55 GMT

If you can edit the code that you posted earlier then I recommend doing that. You could totally re-write it to make it much shorter and clearer. But if you are looking for the smallest possible editing I would go with changing this block of code:

      file 'C:\Users\dlt_gec\Projects\Spritz Norma\Flusso Fatture.txt' dlm=';';

          put
             quote +(-1) ID_FATTURA +(-1) quote
             quote +(-1) ID_CLIENTE +(-1) quote
             quote +(-1) NUM_FATTURA +(-1) quote
             quote +(-1) ID_TIPO_FATTURA +(-1) quote
             quote +(-1) ID_CONDIZIONE_FATTURA +(-1) quote
             quote +(-1) IMPORTO_FATTURA +(-1) quote
             quote +(-1) IMPORTO_RESIDUO_DA_PAGARE +(-1) quote
             quote +(-1) IMPORTO_INCASSATO_CALCOLATO +(-1) quote
             quote +(-1) DATA_SCADENZA_INIZIALE +(-1) quote
             quote +(-1) DATA_EMISSIONE +(-1) quote
             quote +(-1) DATA_SCADENZA_FINALE +(-1) quote
             quote +(-1) NUM_RATE +(-1) quote
             quote +(-1) FLG_FATTURA_RID_INSOLUTO +(-1) quote
             quote +(-1) DATA_REGISTRAZIONE +(-1) quote
             quote +(-1) ID_CONTRATTO +(-1) quote
             quote +(-1) CODICE_FACTOR +(-1) quote
             quote +(-1) CODICE_FACTOR_S +(-1) quote
             quote +(-1) NUM_TOT_RATE_APERTE +(-1) quote
             quote +(-1) DATA_PROX_RATA +(-1) quote
             quote +(-1) ID_STATO_DUNNING +(-1) quote
             quote +(-1) DATA_AVVENUTA_NOTIFICA +(-1) quote
             quote +(-1) DATA_CESSIONE +(-1) quote
             ;

To this block of code:

     file 'C:\Users\dlt_gec\Projects\Spritz Norma\Flusso Fatture.txt' DSD dlm=';';

          put
              ID_FATTURA
              ID_CLIENTE
              NUM_FATTURA
              ID_TIPO_FATTURA
              ID_CONDIZIONE_FATTURA
              IMPORTO_FATTURA
              IMPORTO_RESIDUO_DA_PAGARE
              IMPORTO_INCASSATO_CALCOLATO
              DATA_SCADENZA_INIZIALE
              DATA_EMISSIONE
              DATA_SCADENZA_FINALE
              NUM_RATE
              FLG_FATTURA_RID_INSOLUTO
              DATA_REGISTRAZIONE
              ID_CONTRATTO
              CODICE_FACTOR
              CODICE_FACTOR_S
              NUM_TOT_RATE_APERTE
              DATA_PROX_RATA
              ID_STATO_DUNNING
              DATA_AVVENUTA_NOTIFICA
              DATA_CESSIONE
          ;

This will generate a file without all of those unneeded quotes and also the unneeded spaces. Quotes will added automatically by the DSD option ONLY when the value of the field requires it. (Basically when the value contains the ';' delimiter or an actual quote character.)

For example if you wanted to write a semi-colon delimited line for the three values 'Extra Large',null and 50 the result with just using DSD and DLM=';' would generate a line like:

Extra Large;;50

You could then read that file using SAS with an INFILE statement that had the DSD and DLM=';' option set.

Creating a line like

"Extra Large";"";"50"

Instead might be harder and unless your purpose is to read it with some tool that is not flexible enough to read the simplier file then I wouldn't recommend going through a lot of gyrations to add the quotes.

Re: removing spaces in the blank fields

niespolo — Thu, 10 Nov 2016 10:35:07 GMT

Hi Tom,

I've edited the code as you suggested me, but SAS returns the error " ERROR 23-2: Invalid option name DSO".

This is the block of code the user can edit in the GUI:

/*---- Map the columns ----*/
proc datasets lib = work nolist nowarn memtype = (data view);
delete W1OCPFAE;
quit;

%put %str(NOTE: Mapping columns ...);
proc sql;
   create view work.W1OCPFAE as
      select
         ID_FATTURA,
         ID_CLIENTE,
         NUM_FATTURA,
         ID_TIPO_FATTURA,
         ID_CONDIZIONE_FATTURA,
         IMPORTO_FATTURA,
         IMPORTO_RESIDUO_DA_PAGARE,
         IMPORTO_INCASSATO_CALCOLATO,
         DATA_SCADENZA_INIZIALE,
         DATA_EMISSIONE,
         DATA_SCADENZA_FINALE,
         NUM_RATE,
         FLG_FATTURA_RID_INSOLUTO,
         DATA_EMISSIONE as DATA_REGISTRAZIONE,
         ID_CONTRATTO,
         ID_FACTOR as CODICE_FACTOR,
         NUM_TOT_RATE_APERTE,
         DATA_PROX_RATA,
         ID_STATO_DUNNING,
         DATA_AVVENUTA_NOTIFICA,
         DATA_CESSIONE,
         ID_TIPO_COMMODITY,
         POD_PDR_TLR,
         ID_MOD_PAGAMENTO,
         CODICE_PDR
   from &SYSLAST
   ;
quit;

%let SYSLAST = work.W1OCPFAE;

data _null_;
   set &SYSLAST;
      attrib ID_FATTURA length = $22
         format = $22.
         informat = $22.;
      attrib ID_CLIENTE length = 8
         format = 11.
         informat = 11.;
      attrib NUM_FATTURA length = $22
         format = $22.
         informat = $22.;
      attrib ID_TIPO_FATTURA length = 8
         format = 11.
         informat = 11.;
      attrib ID_CONDIZIONE_FATTURA length = 8
         format = 11.
         informat = 11.;
      attrib IMPORTO_FATTURA length = 8
         format = 15.2
         informat = 15.2;
      attrib IMPORTO_RESIDUO_DA_PAGARE length = 8
         format = 15.2
         informat = 15.2;
      attrib IMPORTO_INCASSATO_CALCOLATO length = 8
         format = 15.2
         informat = 15.2;
      attrib DATA_SCADENZA_INIZIALE length = 8
         format = DATETIME20.
         informat = DATETIME20.;
      attrib DATA_EMISSIONE length = 8
         format = DATETIME20.
         informat = DATETIME20.;
      attrib DATA_SCADENZA_FINALE length = 8
         format = DATETIME20.
         informat = DATETIME20.;
      attrib NUM_RATE length = 8
         format = 4.
         informat = 4.;
      attrib FLG_FATTURA_RID_INSOLUTO length = 8
         format = 2.
         informat = 2.;
      attrib DATA_REGISTRAZIONE length = 8
         format = Datetime20.
         informat = Datetime20.;
      attrib ID_CONTRATTO length = $22;
      attrib CODICE_FACTOR length = 8;
      attrib NUM_TOT_RATE_APERTE length = 8;
      attrib DATA_PROX_RATA length = 8
         format = Datetime20.
         informat = Datetime20.;
      attrib ID_STATO_DUNNING length = 8
         format = 11.
         informat = 11.;
      attrib DATA_AVVENUTA_NOTIFICA length = 8
         format = DATETIME20.
         informat = DATETIME20.;
      attrib DATA_CESSIONE length = 8
         format = DATETIME20.
         informat = DATETIME20.;
      attrib ID_TIPO_COMMODITY length = 8
         format = 11.
         informat = 11.;
      attrib POD_PDR_TLR length = $30
         format = $30.
         informat = $30.;
      attrib ID_MOD_PAGAMENTO length = 8
         format = 11.
         informat = 11.;
      attrib CODICE_PDR length = $8;
      quote='"';
      file 'C:\Users\dlt_gec\Projects\Spritz Norma\Flusso Fatture.txt' DSO dlm=';';

         put
    ID_FATTURA
    ID_CLIENTE
    NUM_FATTURA
    ID_TIPO_FATTURA
    ID_CONDIZIONE_FATTURA
    IMPORTO_FATTURA
    IMPORTO_RESIDUO_DA_PAGARE
    IMPORTO_INCASSATO_CALCOLATO
    DATA_SCADENZA_INIZIALE
    DATA_EMISSIONE
    DATA_SCADENZA_FINALE
    NUM_RATE
    FLG_FATTURA_RID_INSOLUTO
    DATA_REGISTRAZIONE
    ID_CONTRATTO
    CODICE_FACTOR
    NUM_TOT_RATE_APERTE
    DATA_PROX_RATA
    ID_STATO_DUNNING
    DATA_AVVENUTA_NOTIFICA
    DATA_CESSIONE
    ID_TIPO_COMMODITY
    POD_PDR_TLR
    ID_MOD_PAGAMENTO
    CODICE_PDR
            ;

run;

%rcSet(&syserr);

Before this block and later this block, the are other lines of code that cannot be edited by the user. In the attached file there is the entire block of code behind the SAS transformation.

Thank you.

Re: removing spaces in the blank fields

Tom — Thu, 10 Nov 2016 12:22:24 GMT

The option is DSD not dso.

Re: removing spaces in the blank fields

niespolo — Thu, 10 Nov 2016 14:18:04 GMT

Perfect, it works now!