SAS: displaying data in a particular way

Question

(I find it hard to give a good descriptive title, so I'll just ask by means of an example.)

I have a data set like this:

|ID | A1 A2 A3 | B1 B2 B3 | C1 C2 C3 |
+---+----------+----------+----------+
| 1 |  a aa aaa| b  bb bbb| c  cc ccc|
| 2 |  (... some values, etc ...) 

What I want to do is, given an "ID", make a table output with the values A1,A2,etc for that ID, something like this:

|   | A's | B's | C's |
+---+-----+-----+-----+
| 1 |  a  |  b  |  c  |
| 2 |  aa | bb  |  cc |
| 3 | aaa | bbb | ccc |

So, to recap: I want to pick a row, and output a table with certain variables displayed in columns. I've tried to wrap my mind around how proc tabulate works, but haven't managed to wrangle it into giving me what I want; it may be I'm barking up the wrong tree. Is there a way to do this?

I don't need this to return a data table, just some screen output.

Answer 1

Sounds like something that on old style data _null_ report could produce.

data _null_;
  set have ;
  where id=1 ;
  array a a1-a3 ;
  array b b1-b3 ;
  array c c1-c3 ;
  file print;
  put @10 'A' @20 'B' @30 'C' 
   /  @10 8*'-' @20 8*'-' @30 8*'-'
  ;
  do i=1 to dim(a);
   put i 8. @10 a(i) @20 b(i) @30 c(i) ;
  end;
run;

Results

     A         B         C
     --------  --------  --------
   1 a         b         c
   2 aa        bb        cc
   3 aaa       bbb       ccc

Answer 2

You can reshape the data by creating a transposing view that operates on the three arrays in parallel. Proc REPORT or PRINT can then be used to generate the presentation output.

Sample Data

data have;
  do id = 1 to 10;
    array a a1-a3;
    array b b1-b3;
    array c c1-c3;

    do i = 1 to dim(a);
      a(i) = 10 ** i + id;
      b(i) = 2 * 10 ** i + id;
      c(i) = 3 * 10 ** i + id;
    end;
    output;

    keep id a: b: c:;
  end;
run;

Transposing view

data have_v / view = have_v;
  set have;
  array as a1-a3;
  array bs b1-b3;
  array cs c1-c3;
  do seq = 1 to dim(as);
    a = as(seq);
    b = bs(seq);
    c = cs(seq);
    output;
  end;
  keep id seq a b c;
run;

Output with where clause. BY statement used to show id value in output.

proc report data=have_v;
  by id;
  where id = 3;
  column id seq a b c;
  define id / display noprint;    
run;

You could use VIEWTABLE and issue a WHERE command if you don't want to produce output.

If each row encompasses an arbitrary number of 'arrays' (say a to z) of arbitrary but equal length (say 1 to 15), you would want to write a macro that performs some meta-data examination of the data set in question. The examination would attempt to discover the array 'names' and number of elements in each. This say would need to discover and output 15 rows by 26 columns for a given id.