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!!!
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;
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;
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.