Trailing spaces removed when export to txt file

[dcortell]

Hi all people

I'm encountering a weird issue in exporting a sas dataset into a txt file

Details

In a first step, I assign to a series of text variables a specific length. In other word I want that each of these variable will have a specifi dimension and that in case a value is inferior to that dimension then blank spaces will fill the remaining space.

then I concatenate all these varialbes in a unique variable wth the aim to have a fixed total length of 129 character

then I export this one column dataset to txt. And here it comes the problem. While "in between" blank spaces are keep, the trailing ones, referred to the last variable concatenated (Nombre), get removed during the export, while I would keep them, in order to have same overall length

Below the code of the first two step. the export is made through SAs EG user interface (nothing complicated). any idea why sas cut the trailing final spaces and how i can preserve them in the export??

Many thanks

D

%_eg_conditional_dropds(WORK.accounts);
proc sql;
create table finalv1 as 
select

Tipo_persona length=1 format=$1. informat=$char1. ,
Codigo_persona length=9 format=$9. informat=$char9. ,
Empresa_Banco length=4 format=$4. informat=$char4.,
Centro_Sucursal length=4 format=$4. informat=$char4.,
Producto length=3 format=$3. informat=$char3.,
("00" || put(Sub_Tipo_Producto, z1.)) length=3 format=$3. informat=$char3. as Sub_Tipo_Producto,
put(Contract, z7.) length=7 format=$7. informat=$char7. as Num_Contrato,
Fecha_Alta2 length=8 format=$8. informat=$char8. as Fecha_Alta,
Indicador_Alta_Baja length=1 format=$1. informat=$char1.,
Fecha_Baja2 length=8 format=$8. informat=$char8. as Fecha_Baja,
Id_fiscal  length=9 format=$9. informat=$char9. as Id_Fiscal,
t2.Apellido_1 length=26 format=$26. informat=$char26. as Apellido_1 ,
t2.Apellido_2 length=26 format=$26. informat=$char26. as Apellido_2 ,
t2.Nombre length=20 format=$20. informat=$char20. as Nombre ,
"  "  length=2 format=$2. informat=$2. as Final_de_registro
from work.new_contract t1 left join work.idselected t2 on (t1.acct11=t2.acct11)
order by Num_Contrato
;
quit;


proc  sql;
create table unified as
select
cat(Tipo_persona,
Codigo_persona,
Empresa_Banco,
Centro_Sucursal,
Producto,
Sub_Tipo_Producto,
Num_Contrato,
Fecha_Alta,
Indicador_Alta_Baja,
Fecha_Baja,
Id_Fiscal,
Apellido_1,
Apellido_2,
Nombre) length=129 format=$129. informat=$char129. as finals
from work.finalv1;
quit;

[jklaverstijn]

Hi @dcortell,

We cannot see ther code you use to write to the file. But that's bound to be a datastep.

If you put the one var contyaining the whole record make certain you use a format. That will include the trailing whitespace.

data _null_;
   file ' outfile.txt';
   set unified;
   put finals $129.;
run;

Hope this helps,

- Jan.

[dcortell]

Hi @jklaverstijn this is the code

%_eg_conditional_dropds(WORK.accounts);
proc sql;
create table finalv1 as
select

Tipo_persona length=1 format=$1. informat=$char1. ,
Codigo_persona length=9 format=$9. informat=$char9. ,
Empresa_Banco length=4 format=$4. informat=$char4.,
Centro_Sucursal length=4 format=$4. informat=$char4.,
Producto length=3 format=$3. informat=$char3.,
("00" || put(Sub_Tipo_Producto, z1.)) length=3 format=$3. informat=$char3. as Sub_Tipo_Producto,
put(Contract, z7.) length=7 format=$7. informat=$char7. as Num_Contrato,
Fecha_Alta2 length=8 format=$8. informat=$char8. as Fecha_Alta,
Indicador_Alta_Baja length=1 format=$1. informat=$char1.,
Fecha_Baja2 length=8 format=$8. informat=$char8. as Fecha_Baja,
Id_fiscal length=9 format=$9. informat=$char9. as Id_Fiscal,
t2.Apellido_1 length=26 format=$26. informat=$char26. as Apellido_1 ,
t2.Apellido_2 length=26 format=$26. informat=$char26. as Apellido_2 ,
t2.Nombre length=20 format=$20. informat=$char20. as Nombre ,
" " length=2 format=$2. informat=$2. as Final_de_registro
from work.new_contract t1 left join work.idselected t2 on (t1.acct11=t2.acct11)
order by Num_Contrato
;
quit;

proc sql;
create table unified as
select
cat(Tipo_persona,
Codigo_persona,
Empresa_Banco,
Centro_Sucursal,
Producto,
Sub_Tipo_Producto,
Num_Contrato,
Fecha_Alta,
Indicador_Alta_Baja,
Fecha_Baja,
Id_Fiscal,
Apellido_1,
Apellido_2,
Nombre) length=129 format=$129. informat=$char129. as finals
from work.finalv1;
quit;

[dcortell]

@jklaverstijn As per my code I was already using formats but still they help in keep the "within" spaces but not the trailing spaces of the last variable concatenated

Thanks

D

[Reeza]

Sounds like your trying to generate a fixed width file. Instead of the export facility try a manual data step. 

See the link below on how to create the file. 

http://www.ats.ucla.edu/stat/sas/faq/write_fixedformat.htm

[jklaverstijn]

Sorry D, but all I see is two steps that create a SAS data set. These do not result in a text file on the OS. That step is still missing. Please run the code I presented and tell me if that gives what you were looking for. The formats I see you using indeed result in proper spacing inside the variable as a result from the CAT() function. It is when writing that variable to an external file where you loose the trailing space unless you use a format in the PUT statement.

If that works consider implementing the logic of your SQL in that same datastep, thus eliminating a step and avoiding passes through your data.

- Jan.

[RW9]

Is it a fixed width file you want to output, then you can print each field at a given point on the line using @:

data _null_;

  file...;

  set...;

  put @1 var1  @11 var2...;

run;

[Astounding]

If all else fails ...

create a field that is $130 instead of $129.  Just add a nonblank character at the end that you can later discard.