DATA Step, Macro, Functions and more

Transpose error: missing numeric suffix on a numbered variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Transpose error: missing numeric suffix on a numbered variable

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:

old table.png

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

Table new.png

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!!!


Accepted Solutions
Solution
‎01-07-2015 05:43 PM
Super User
Super User
Posts: 6,502

Re: Transpose error: missing numeric suffix on a numbered variable

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;

View solution in original post


All Replies
Solution
‎01-07-2015 05:43 PM
Super User
Super User
Posts: 6,502

Re: Transpose error: missing numeric suffix on a numbered variable

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;

Occasional Contributor
Posts: 13

Re: Transpose error: missing numeric suffix on a numbered variable

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!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 2301 views
  • 0 likes
  • 2 in conversation