Array for transposing data from wide to long form

Solved
Occasional Contributor
Posts: 17

Array for transposing data from wide to long form

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!

Accepted Solutions
Solution
‎02-12-2017 12:31 AM
Posts: 1,309

Re: Array for transposing data from wide to long form

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;

All Replies
Super User
Posts: 13,295

Re: Array for transposing data from wide to long form

[ Edited ]

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.

Occasional Contributor
Posts: 17

Re: Array for transposing data from wide to long form

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

Super User
Posts: 13,295

Re: Array for transposing data from wide to long form

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

SAS Employee
Posts: 20

Re: Array for transposing data from wide to long form

``````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;
``````
Solution
‎02-12-2017 12:31 AM
Posts: 1,309

Re: Array for transposing data from wide to long form

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;

☑ This topic is solved.