BookmarkSubscribeRSS Feed
dcortell
Pyrite | Level 9

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;
7 REPLIES 7
jklaverstijn
Rhodochrosite | Level 12

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
Pyrite | Level 9

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
Pyrite | Level 9

@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
Super User

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
Rhodochrosite | Level 12

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
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2973 views
  • 1 like
  • 5 in conversation