topic Transpose in Data Step in SAS Programming

Transpose in Data Step

jmontefalcon — Tue, 26 Mar 2024 21:30:49 GMT

Due to the nature of how the data was collected, the data looks like this and is in txt file:

Bone 1 ID	Bone 2 ID	Bone 3 ID	Bone 1 Type	Bone 2 Type	Bone 3 Type	Bone 1 Condition	Bone 2 Condition	Bone 3 Condition
123	156	182	0	1	0	1	2	3

I want to read the txt file in and have it look like this:

Bone ID	Bone Type	Bone Condition
123	0	1
156	1	2
182	0	3

Please let me know how I can achieve this. All variables are character variables. ID length should be $3. Bone type and bone condition should be a length of $1. Thank you!

Re: Transpose in Data Step

ballardw — Tue, 26 Mar 2024 22:26:57 GMT

What you show for the source is not text, but a formatted display table of some flavor. Best would be to copy a few rows of data from the source and then paste it into a text box opened on the forum with the </> icon that appears above the message box. The text box prevents reformatting of pasted text and pretty much shows what you have.

Is the text file delimited with some character like a comma, space or TAB between values or do the values appear in fixed column positions? This is important as the input statement needs information about that.

Also are there always the exact same number of items on every row?

If some of your data rows look like:

Bone 1 ID	Bone 2 ID	Bone 1 Type	Bone 2 Type	Bone 1 Condition	Bone 2 Condition
123	156	0	1	1	2

and others as shown this may be a moderately complicated bit. Which is why I suggested several rows (5 to 10 should do for basics).

Here is an example that will read a line that always has 3 sets of values.

data example;
  length boneid $ 3 bonetype bonecondition $ 1;
  input @;
  do i=1 to 3;
     boneid= scan(_infile_,i);
     bonetype= scan(_infile_,i+3);
     bonecondition = scan(_infile_,i+5);
     output;
  end;
  drop i;
datalines;
123 156 182 0 1 0 1 2 3
;

The input without any variables reads the line into a single automatic variable named _INFILE_ then a loop that expects to read exactly 3 values 3 times uses some arithmetic to determine the position in the _INFILE_ that a value should be and uses SCAN to extract. This particular example is going default to space as the delimiter though other characters such as comma would work as well (look up SCAN for the defaults).

However additional logic is needed if different numbers of sets of values are on a single line (and even more, if anything else appears).

Re: Transpose in Data Step

Tom — Wed, 27 Mar 2024 01:02:06 GMT

That is not what a TEXT file looks like. A text is lines of TEXT not a SPREADSHEET with grid lines drawn on the screen.

So assuming you meant that the data looks like this (perhaps with a different delimiter character)

Bone 1 ID|Bone 2 ID|Bone 3 ID|Bone 1 Type|Bone 2 Type|Bone 3 Type|Bone 1 Condition|Bone 2 Condition|Bone 3 Condition
123|156|182|0|1|0|1|2|3

Then you could read it using something like this:

Read the header row into a temporary array.

For the other rows read each value and split the header part into the NAME part and BONE number part.

Then re-order so the values for each BONE number are in order.

Finally use PROC TRANSPOSE to convert it into a normal looking dataset.

data tall ;
  infile text dsd dlm='|' truncover length=ll column=cc;
  if _n_=1 then do;
    array headers[5000] $50 _temporary_;
    do cols=1 by 1 until(cc>ll);
      input headers[cols] @;
    end;
    retain cols;
    delete;
  end;
  row+1;
  do col=1 to cols;
    length bone 8 _name_ $32 ;
    _name_=scan(headers[col],-1,' ');
    bone=input(scan(headers[col],2,' '),32.);
    input value :$3. @;
    output;
  end;
  drop cols;
run;

proc print;
run;

proc sort;
  by row bone col;
run;

proc transpose data=tall out=wide(drop=_name_) ;
  by row bone;
  id _name_;
  var value;
run;

proc print;
run;

Results: TALL

Obs    row    col    bone    _name_       value

 1      1      1       1     ID            123
 2      1      2       2     ID            156
 3      1      3       3     ID            182
 4      1      4       1     Type          0
 5      1      5       2     Type          1
 6      1      6       3     Type          0
 7      1      7       1     Condition     1
 8      1      8       2     Condition     2
 9      1      9       3     Condition     3

WANT

Obs    row    bone    ID     Type    Condition

 1      1       1     123     0          1
 2      1       2     156     1          2
 3      1       3     182     0          3