Search code examples
ms-accesssasexporttext-filesproc

how to make proc export in SAS to be aligned similar to an Access output?

Currently I have a file in Access that I am trying to recreate in SAS.

At the moment the Access file has the same columns and counts etc as the SAS table I am able to create, the only issue is when I export to a .txt which Access does as well, it does not align properly.

Access Aligns like below (even when all/any variables are different lengths) 

A bbbbbb ccccc dddd e f g hhhhhhh I JK 
A bbbbbb ccccc dddd e f g hhhhhhh I JK

SAS Aligns like below (only the first 5 columns align)

A     bbb ccc  dd  e f g hhhh I JK 
A bbbbbb ccccc dddd e f g hhhhhhh I JK

But what confuses me is that the 3rd column variable is a counter n and there are thousands of lines, yet it still keeps its alignment and spaces out the same up until the 5th line.

I have formatted all of my variables using $10. etc so I know I have them set up correctly (to an extent)

I would like advice on how to make the .txt output similar to access since it get ingested into a system and has to have the columns strictly defined.

I am open to exporting as an excel or other format and making a few adjustments to make it look delimited by spacing or something. Currently I have tried to test it but my huge macros do not make outputting new large code easy.

Anything would be appreciated - thanks

Solution

  • Writing fixed column text files is easy in SAS. Let's check what columns your sample data fields are using.

             1         2         3         4
----+----0----+----0----+----0----+----0
A bbbbbb ccccc dddd e f g hhhhhhh I JK

    So A is just using column 1, B is using columns 3 to 8, C is using columns 10 to 14, etc.

    So if A--K are all defined as character as you say then you could just use something like.

    data _null_;
  set have;
  file 'want.txt' ;
  put a $1. +1 b $6. +1 c $5. +1 d $4. +1 e $1. +1 
      f $1. +1 g $1. +1 h $7. +1 i $1. +1 j $1. k $1.
  ;
run;

    Now if some of the fields are numbers instead of characters then you can change the formats. So if field C is actually a number then you would need to use 5. format instead of $5. format. Or perhaps even Z5. format to have it add leading zeros.