topic Re: Array for transposing data from wide to long form in SAS Data Management

Array for transposing data from wide to long form

simkinm2 — Thu, 09 Feb 2017 20:27:27 GMT

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!

Re: Array for transposing data from wide to long form

ballardw — Thu, 09 Feb 2017 21:53:21 GMT

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.

Re: Array for transposing data from wide to long form

simkinm2 — Thu, 09 Feb 2017 20:55:06 GMT

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".

Re: Array for transposing data from wide to long form

ballardw — Thu, 09 Feb 2017 21:57:32 GMT

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});

Re: Array for transposing data from wide to long form

MichaelLarsen — Fri, 10 Feb 2017 12:29:37 GMT

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;

Re: Array for transposing data from wide to long form

mkeintz — Sun, 12 Feb 2017 02:24:06 GMT

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;