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

Question

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

Ok, we can't really see what your data looks like because there's no formatting in your question. Most likely you need do this semi-manually using a data step and PUT statements because it looks like you want a FIXED length format output. PROC EXPORT doesn't do that as far as I know. — Reeza
It is very easy to create a fixed column text file from a SAS dataset. What code did you try to use to do that? What is the definition of the file you want to create? — Tom
I am currently using Proc Export Data=work. Outfile="C:\Data\Out.txt" DBMS=TAB REPLACE; PUTNAMES=NO; RUN; sorry don't know how to format yet... — Joyce L

Tom Tom · Accepted Answer · 2017-08-01T19:07:26

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.

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

1 Answers