topic Re: Proc Import -- Multiple Spaces Between Columns in SAS Programming

Re: Proc Import -- Multiple Spaces Between Columns

larryn3 — Tue, 11 Jan 2022 20:20:23 GMT

Follow-up/related question to :

https://communities.sas.com/t5/SAS-Programming/Proc-Import-Multiple-Spaces-Between-Columns/m-p/789575#M252668

Hi,

This works great for text followed by numerics.

I have sort of a related question.

I need to process many text files so that they can be imported into SAS one by one (I'll do a macro loop and then an append each).

They are all formatted "tables", i.e. with the same number of columns (8) which are all aligned and separated by spaces but the number of spaces can vary and each line is ended with [CRLF].

Here is an example of two of them:

FILE 1

N22OR44DLICHT, MxxAdK, b x26 OWNEdd RY) adLATINUM 01/2002 B Y N 304gg438g97

134PO5TEAT, PffAfU, AdsfO x26 CHIEF sf323+R ALfdL 335gr 06/2007 NA Y N 3f5g77r
M11Af N43 GRsfsgANTfOR yy50 OWdaNERd- PLATsd(NsdaY) 03/2009 D Y N x-xf3$x$5xxx

File 2

M11Af N43 GRsfsgANTfOR y50 OWdaNERd- PLATsd(NsdaY) 03/2009 D Y N x-xf3$x$5xxx
PLfsf33ATINUM CRdsfT yy50 GENE223RAL Pada2sARTNER - 09/2005 NA Y N 226

The output should be a dataset with 8 variables: C1....C8. The variables should each be text variables.

Any ideas?

Thank you for your help.

Larry

Re: Proc Import -- Multiple Spaces Between Columns

Tom — Tue, 11 Jan 2022 20:21:53 GMT

Your file appears to be in fixed columns, but it is hard to tell since you pasted it into the body of your message instead of using the Insert Code button to get a pop-up window to paste the lines.

Re: Proc Import -- Multiple Spaces Between Columns

larryn3 — Tue, 11 Jan 2022 20:33:30 GMT

Forgive my ignorance, but i don't see the popup.

Re: Proc Import -- Multiple Spaces Between Columns

ballardw — Tue, 11 Jan 2022 20:45:05 GMT

@larryn3 wrote:
Forgive my ignorance, but i don't see the popup.

Above the main message would in most browsers a number of icons appear for different actions. The </> opens a window for pasting plain text. The small "running man" opens a window for pasting SAS code which will have some code formatting applied such as coloring keywords.

For text data the text window is preferred though the code window will work. Neither changes the content of pasted text. The main message windows on this forum will reformat pasted text, typically reducing "white space" such as removing space characters and may insert not-visible HTML tags.

Re: Proc Import -- Multiple Spaces Between Columns

Tom — Tue, 11 Jan 2022 20:47:44 GMT

The menu bar appears right above the text box.

Re: Proc Import -- Multiple Spaces Between Columns

Tom — Tue, 11 Jan 2022 20:53:59 GMT

To see how you lines line up try using the LIST statement in a data step.

For example this will dump the first 10 lines of 'file1'.

data _null_;
  infile 'file1' obs=10;
  input;
  list;
run;

Re: Proc Import -- Multiple Spaces Between Columns

larryn3 — Tue, 11 Jan 2022 21:16:18 GMT

Ah, this shows me the column locations where the variables should begin. Now how do I put the "column start"s into a substring function to pull out the 8 variables.

How do I do that?

really appreciate your help, I think we're on the right track.

Re: Proc Import -- Multiple Spaces Between Columns

Tom — Tue, 11 Jan 2022 21:48:36 GMT

No need for splitting strings. Just tell SAS where to read the values.

data want;
  infile 'file1' ;
  input c1 $1-10 c2 11-15 .... ;
run;

https://documentation.sas.com/doc/en/pgmsascdc/v_018/lestmtsref/n13ejk9swz5vrbn0z34iazfrp0wp.htm

Re: Proc Import -- Multiple Spaces Between Columns

larryn3 — Tue, 11 Jan 2022 22:02:21 GMT

Maybe I'm missing something

I don't know which position to read each the values from. Each of the files has can have a different column position for each of the eight variables I want to create.

The only thing I do know is that there are 8 columns in each file and that the each of the columns begins at the same position. I hope I am being clear. If not I can provide a more detailed explanation.

Re: Proc Import -- Multiple Spaces Between Columns

Tom — Tue, 11 Jan 2022 22:36:28 GMT

It is not clear what you mean. You say you know where the values are on the line and then you turn around and say you don't know. Which is it?

Post an example of the file. Show what you want to create from that example.

It sounds like your question is that you have 8 different files with slightly different layouts.

Why not just make 8 different data steps to read each different file?

Re: Proc Import -- Multiple Spaces Between Columns

larryn3 — Tue, 11 Jan 2022 22:49:57 GMT

Actually I have about 1000 files each file has 8 columns but the columns are in different positions:

File 1:

C1 C2 C3 c4 c5 c6 c7 c8

aaaa z8z 23 bbbbbbbbbbbbb s;lfjslf;sjl;dkfjs y ls;dkfjl ;ds n abc xyz

aafssb 122 bb111233bbb 54332fjs n l4645643dsj n abc xyz2342221

File 2

C1 C2 C3 c4 c5 c6 c7 c8

aaaa zzz zz bbbb s;lfjslf;sjl;dkfjs y ls;dkfjl ;ds n 223 xyz

aafssfaa b zz zz b33bbb 54332fjs n l46456ds n a b qr4dsdfs

Re: Proc Import -- Multiple Spaces Between Columns

larryn3 — Tue, 11 Jan 2022 22:56:43 GMT

Sorry, Unfortunately the columns didn't line up the way they appeared when i was typing them in.

I tried try the "insert code" but that was not any better. I'll try to put the example in a file and attach it to the next post.

Re: Proc Import -- Multiple Spaces Between Columns

larryn3 — Tue, 11 Jan 2022 23:08:02 GMT

Attached is a word file which should show more clearly the way the columns are aligned.

Re: Proc Import -- Multiple Spaces Between Columns

Tom — Tue, 11 Jan 2022 23:08:30 GMT

Copy the lines from the SAS log when you ran the data step with the LIST statement. Include the ruler line it produces. Open a new reply on the forum and click on the Insert Code button (looks like < / > ) and paste the lines into the window that pops up. Click the OK button. Then click the POST button to publish your reply.

Re: Proc Import -- Multiple Spaces Between Columns

larryn3 — Tue, 11 Jan 2022 23:18:00 GMT

File 2

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
1         N22OR44DLICHT, MxxAdK,  b             x26     OWNEdd RY) adLATINUM              
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
      81                                             01/2002        B         Y         N 
     161   304gg438g97                                                                    
2         134PO5TEAT, PffAfU, AdsfO             x26     CHIEF sf323+R ALfdL 335gr         
      81                                             06/2007        NA        Y         N 
     161   3f5g77r                                                                        
NOTE: 3 records were read from file 'c:\test\file1a.txt'
      The minimum record length was 0
      The maximum record length was 172
NOTE: The data step took :
      real time : 0.001
      cpu time  : 0.000


File2
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
1         M11Af N43 GRsfsgANTfOR                       yy50    OWdaNERd- PLATsd(NsdaY)    
      81                                                        03/2009     D      Y      
     161    N     x-xf3$x$5xxx                                                            
2         PLfsf33ATINUM CRdsfT                         yy50    GENE223RAL Pada2sARTNER - P
      81  LATIsdaNU CRE23                                       09/2005     NA     Y      
     161    N     www

Re: Proc Import -- Multiple Spaces Between Columns

ballardw — Tue, 11 Jan 2022 23:28:37 GMT

@larryn3 wrote:

Attached is a word file which should show more clearly the way the columns are aligned.

Actually a word processor document is likely to be worse than plain text. If by any chance your data contains TAB characters the "alignment" almost certainly will not be what should be read.Also non-visible formatting characters can sneak into the file making copy out and read a headache as well.

If the file is TEXT then post actual text. Don't type anything on this forum. Open a text box using the </> icon. Copy lines from the file after it is opened in a plain text file editor like NOTEPAD or even the SAS Editor. Then paste those lines into the text box.

Re: Proc Import -- Multiple Spaces Between Columns

larryn3 — Tue, 11 Jan 2022 23:32:27 GMT

File 1

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
1         N22OR44DLICHT, MxxAdK,  b             x26     OWNEdd RY) adLATINUM              
      81                                             01/2002        B         Y         N 
     161   304gg438g97                                                                    
2         134PO5TEAT, PffAfU, AdsfO             x26     CHIEF sf323+R ALfdL 335gr         
      81                                             06/2007        NA        Y         N 
     161   3f5g77r      

File 2

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
1         M11Af N43 GRsfsgANTfOR                       yy50    OWdaNERd- PLATsd(NsdaY)    
      81                                                        03/2009     D      Y      
     161    N     x-xf3$x$5xxx                                                            
2         PLfsf33ATINUM CRdsfT                         yy50    GENE223RAL Pada2sARTNER - P
      81  LATIsdaNU CRE23                                       09/2005     NA     Y      
     161    N     www

Re: Proc Import -- Multiple Spaces Between Columns

Tom — Tue, 11 Jan 2022 23:35:07 GMT

Read the first line with the C1 .... in it and use that to calculate the starting locations for this version of the file.

Let's convert one of your example files into a physical file we can reference in the example code.

options parmcards=txt ;
filename txt temp;
parmcards4;
C1           C2           C3                                                   c4                                      c5                      c6                    c7     c8

aaaa        z8z  23    bbbbbbbbbbbbb                             s;lfjslf;sjl;dkfjs                    y                        ls;dkfjl  ;ds       n       abc xyz
;;;;

Now let's read that first line and pick out the variable names and their start and end columns.


data start;
  length col start end 8 name next $32;
  infile txt obs=1 column=cc truncover;
  start=1;
  input name @;
  do col=1 by 1 until(next=' ');
    input next @ ;
    end = cc - lengthn(next) - 2;
    output;
    name=next;
    start = end+1;
  end;
  list;
  drop next;
run;

Result:

Obs    col    start    end    name

 1      1        1      13     C1
 2      2       14      26     C2
 3      3       27      79     C3
 4      4       80     119     c4
 5      5      120     143     c5
 6      6      144     165     c6
 7      7      166     171     c7
 8      8      172     173     c8

Note that the end column for that last one might be a little off as the length of the NAME is probably shorter than the length of the values in the last column.

We can use that to write the input statement to read your file.

filename code temp;

data _null_;
  set start end=eof;
  file code;
  if _n_=1 then put 'input ' ;
  put @3 name '$' start '-' end ;
  if eof then put ';' ;
run;

data want;
  infile txt firstobs=2 truncover ;
  %include code / source2;
run;

Re: Proc Import -- Multiple Spaces Between Columns

Tom — Tue, 11 Jan 2022 23:40:38 GMT

You are going to have figure out the pattern yourself I am afraid.

Is there no documentation provided that explains why the different files use different widths for the fields?

Re: Proc Import -- Multiple Spaces Between Columns

larryn3 — Wed, 12 Jan 2022 12:43:59 GMT

Thank you very much for all your help and the time you spent on this. I'm going to study your suggestion in the next couple of days. It is possible I may need to follow up.