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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.