SAS: Using an array to transpose a table

Question

I'm attempting to recreate some code which does the opposite of what is on page 9-10 on http://support.sas.com/resources/papers/proceedings10/158-2010.pdf. So instead of making a table go from wide to long, I'd like it to become long to wide.

Id Col1  Col2     
1  Val1  A    
1  Val2  B    
2  Val1  C    
2  Val3  D  
3  Val2  E

Transposes to:

Id X_Val1 X_Val2 X_Val3  
1  A      B      .
2  C      .      D
3  .      .      E

Any ideas on how I should be going about this? I know I should be using an array and trying to create a new column X_Val1 where X_Val1 = cat('X',Val1) where X is some string.

DomPazz · Accepted Answer · 2013-10-18 05:04:29Z

2

You need to first figure out how many variables you need. Then you can create the variables, use an array, and assign the values.

data test;
input id col1 $ col2 $;
datalines;   
1  Val1  A    
1  Val2  B    
2  Val3  C    
2  Val4  D   
2  Val5  E
;
run;

/*Need to get the number of variables that need to be created*/
proc sql noprint;
select max(c)
    into :arr_size
    from
    ( select ID, count(*) as c
        from test
        group by id
    );
quit;

/*Get rid of leading spaces*/
%let arr_size=%left(&arr_size);
%put &arr_size;

data test_t;
set test;
by id;
/*Create the variables*/
format SOME_X1 - SOME_X&arr_size $8.;
/*Create an array*/
array SOME_X[&arr_size];

/*Retain the values*/
retain count SOME_X:;

if first.id then do;
count = 0;
do i=1 to &arr_size;
    SOME_X[i] = "";
end;
end;

count = count + 1;
SOME_X[count] = col2;

if last.id then
    output;

keep id SOME_X:;
run;

edited Oct 18, 2013 at 5:04

answered Oct 18, 2013 at 4:48

DomPazz

12.4k19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Black Over a year ago

Hi DomPazz, thank you for the comment. I figured it out independently but if I come across the problem once again in a more general context, I'll refer to your answer. Thank again :)

Jay Corbett Over a year ago

If you have SAS 9.3 you can use the TRIMMED option (i.e. into :arr_size trimmed) to remove leading and trailing blanks when a numeric is selected into a macro var. Also, if you are going to use the %LET assignment statement to remove leading and trailing blanks there is no need for any macro functions, at least in ver 9 or greater.

Jay Corbett Over a year ago

That sql code to find the max number of IDs is really useful, thanks

Joe · Accepted Answer · 2013-10-18 19:12:38Z

2

I have no idea why you would want to do this with anything other than PROC TRANSPOSE.

proc transpose data=have out=want prefix='X_';
by id;
id col1;
var col2;
run;

answered Oct 18, 2013 at 19:12

Joe

63.5k7 gold badges51 silver badges70 bronze badges

Collectives™ on Stack Overflow

SAS: Using an array to transpose a table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related