Transpose error: missing numeric suffix on a numbered variable

[a2veeram]

Hello,

I am trying to transpose a table, but variable is not numeric - it's a mix of characters and numbers (e.g. 1A, 1B, 1C, 2A, 2B, 2C, 2D, 2E, 3A, etc.... ).

When I am using a regular data step it shows an error "missing numeric suffix on a numbered variable" and "Mixing of implicit and explicit array subscripting is not allowed".

My table looks like this:

And I am trying to convert it to this (per every student - a cumulative score for each of the programs:

I am using:

1      data new;

2      set old;

3      by ID;

4      KEEP ID FDBNS1A-FDBNS6C;

5      RETAIN FDBNS1A-FDBNS6C;

6      ARRAY aFDBNS (1A ; 6C) FDBNS1A-FDBNS6C;

7      IF first.ID then

8      DO;

9          DO i = 1A to 6C;

10         aFDBNS ( i ) = 0;

11      END;

12      END;

13           aFDBNS (FDGRP) = FDBNS;

14      IF last.ID then output;

15      run;

Lines 4, 5, 6 give "missing numeric suffix on a numbered variable" error;

Lines 10, 13 - "mixing of implicit and explicit array subscripting is not allowed".

I tried using ' ' as for a character variable but it did not work... Is it possible to transpose this way at all..?

Also..... Do I need to include any extra step to make sure that   if the same ID has a few courses (VAR1) that correspond to the same program and level (FDGRP) then the scores will be summed (like in the new table - underlined values).

Thanks so much!!!

[Tom · Accepted solution]

Why not just use PROC TRANSPOSE?  Run it through PROC SUMMARY or PROC SQL first to create sum for multiple observations.

proc transpose data=have out=want (drop=_name_) prefix=fdbns ;

  by id ;

  id fdgrp ;

  var fdbns ;

run;

[a2veeram]

Thank you so much Tom!

I used proc freq to get the totals (instead of proc summary / proc sql) and then used proc transpose. That worked equally well!