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!
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 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.
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".
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});
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.