modifying my table

[billseol]

Hi,

my data is just a list of words (txt file) and I managed to import data into a table.

Since the lines have different number of words, the imported data have different number of columns.

The row with the most columns has 42 columns. 

What I want to do is if a row has more than 10 columns (that is, if a row has more than 10 words) move the following columns to the next row

For example (5 columns in this case)

if I have this as my data,

a b c d e f g h i

a b c d 

a b c d e f g

a b 

i want to make

a b c d e

f g h i

a b c d

a b c d e

f g 

a b

Can I do this in SAS?

[Reeza]

You can but it would be better to change the import program used to read the data instead.

[billseol]

which program would you suggest?

[PaigeMiller]

Hard to see a benefit of this somewhat arbitrary re-arranging of the data.

If you have a b c d e f g h i then you know f is in the sixth position.

If you modify this to a b c d e in row 1 and f g h i in row 2, then a in row 1 and f in row 2 are in the same columns and have the same variable name, and you lose the relationship between the character strings in the original data.

Why do you want to do this?

[billseol]

technically, the data i have doesn't have any relationship between each value. I just want the data in set number of columns without changing the order of the data or the order of each row.

[PaigeMiller]

I understand that. I still don't see any benefit from doing this conversion of the data to have just 5 in a row. What can you do with this conversion that you wouldn't be able to do if you left it unchanged?

[billseol]

It's just for my own interest.
Ok let me change my question then.
Is there a way to move certain values to another location?

[PaigeMiller]

Well, I'm sure there is a way, but I don't have any brilliant ideas right now. Maybe some of the hash experts in the SAS Communities can do this quickly (but I am not one of them).

[Tom]

One variable would be better than 5 or 10.

If you have a unique row identifier just use PROC TRANSPOSE.

proc transpose data=have out=want;
  by rowid;
  var var1-var10;
run;

[Shmuel]

Check next tested code:

data have; 
 retain nwords;
 infile cards truncover;
 input text $char80.;
 nwords = countw(text);
cards;
a b c d e f g h i
a b c d 
a b c d e f g
a b 
; run;

%let maxo=4;
data want(keep=wout1-wout&maxo);
     length wout1-wout&maxo $5;
 set have; 
     array wordot {&maxo} $ wout1-wout&maxo; 
     j=0; i=0;
     do until (i=nwords);
        do until (j=&maxo or i=nwords);
           j = j+1; i=i+1; put i= j=;
           wordot(j) = scan(text,i);
        end;
        if wout1 ne ' ' then output;
        do j=1 to &maxo; wordot(j) = ' '; end;
        j=0;
     end;
run;

Why are you splitting the text into columns of one word each ?

Why not just splitting the long text into limited length  sub-strings ?

[Tom · Accepted solution]

It is not hard to do when just reading from a text.  First let's make a sample text file.

options parmcards=txt;
filename txt temp;
parmcards4;
a b c d e f g h i
a b c d
a b c d e f g
a b
;;;;

Now just read 5 words at a time and hold the line. Stop when you have read past the end of the line.

data want ;
  infile txt truncover length=ll column=cc ;
  row+1;
  do until(cc>ll);
    input (v1-v5) (:$10.) @;
    output;
  end;
run;

Results:

Obs    row    v1    v2    v3    v4    v5

 1      1     a     b     c     d     e
 2      1     f     g     h     i
 3      2     a     b     c     d
 4      3     a     b     c     d     e
 5      3     f     g
 6      4     a     b

[billseol]

This is exactly what I wanted.
Thank for the simple method.
I really appreciate it.

[Ksharp]

data want ;
  infile cards truncover ;
  row+1;
  input (v1-v5) (:$10.) @;
  do while( cmiss(of v1-v5) <5);
    output;
    input (v1-v5) (:$10.) @; 
  end;
cards;
a b c d e f g h i
a b c d
a b c d e f g
a b
;