Array for transposing data from wide to long form

[simkinm2]

Hello,

I have data in the current format:

Company  Composite_1 Composite_2 (goes through 13)

    A                   75%               60%

    B                   72%               90%

    C                   40%               42%

And I would like my data to be transformed intot his format:

Composite      Percent   Company

   Comp1           75%           A

  Comp2           60%           A

  Comp 1          72%           B

  Comp2            90%           B

  Comp1           40%           C

   Comp2           42%           C

I've tried the following array but only get the company column:

data comp_long;
set comp_wide;
length composite $ 10.;
array v(*) _numeric_;
do i = 1 to dim(v) by 13;
composite = scan (vname(v{i}), 1, "-");
Comp1 = v{i}; comp2 =  v{i+1}; comp3 = v{i+1+1}; comp4 = v{i+1+1+1}; comp5 = v{i+1+1+1+1}; comp6 =v{i+1+1+1+1+1}; comp7 = v{i+1+1+1+1+1+1};
comp8 = v{i+1+1+1+1+1+1+1}; comp9 = v{i+1+1+1+1+1+1+1+1}; comp10 = v{i+1+1+1+1+1+1+1+1+1}; comp11 = v{i+1+1+1+1+1+1+1+1+1+1}; 
comp12 = v{i+1+1+1+1+1+1+1+1+1+1+1}; comp13 = v{i+1+1+1+1+1+1+1+1+1+1+1+1};
output; end; keep company; run; 

I know I'm missing something, so any suggestions are grealty appreciated! Thank you! 

[ballardw]

This may do a bit closer to your posted requirement:

data comp_long (keep = company percent composite);
   set comp_wide;
   length composite $ 10.;
   array v(*) _numeric_;
   do i = 1 to dim(v) ;
      composite = scan (vname(v{i}), 1, "_");
      percent = v[i];
      output; 
   end;  
run; 

Your previous code would have generated numerous errors. It helps to post errors with code. Use results from the Log pasted into a code box opened with the {i} menu icon.

NOTE: removed BY 13 from the DO statement.

[simkinm2]

Thanks so much for your quickl reply.  Oddly enough, I got no errors with my code.  Your code is almost what I need. I'd like to get the entire Composite name in the column, so comp_1, comp_2, etc.  rather than just "comp". 

[ballardw]

I didn't catch your "by 13" the first time around. The By means that the I value went from 1 to 14 and didn't do a second iteration. Without the 'by 13' then you would have generated lots of errors about array subscript out of bounds. ie 10+1+1+1+1 > then the 13 of the array

If you didn't want to trim the name of the comp variable it then why use scan?

composite = vname(v{i});

[MichaelLarsen]

How about using PROC TRANSPOSE instead?

data have;
  input Company $ Composite_1-Composite_13;
  format Composite_1-Composite_13 percent5.2;
  idvar = 'Percent';
cards;
 A 0.75 0.60 0.75 0.60 0.75 0.60 0.75 0.60 0.75 0.60 0.75 0.60 0.75
 B 0.72 0.90 0.72 0.90 0.72 0.90 0.72 0.90 0.72 0.90 0.72 0.90 0.72 
 C 0.40 0.42 0.40 0.42 0.40 0.42 0.40 0.42 0.40 0.42 0.40 0.42 0.40 
 ;
 run;
proc transpose data=have out=want name=Composite ;
  by Company;
  id idvar;
  var Composite_1-Composite_13;
run;

[mkeintz · Accepted solution]

Since the composite of the varnames is going to be a constant, then you can include this code, which calculates it just once:

length big_long_composite $120;

retain big_long_composite;

if _n_=1 then do I=1 to dim(vars);

  big_long_composite=catx(',',vname(vars{i}));

end;