BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
simkinm2
Calcite | Level 5

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

5 REPLIES 5
ballardw
Super User

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
Calcite | Level 5

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
Super User

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
SAS Employee

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
PROC Star

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;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 5 replies
  • 2307 views
  • 0 likes
  • 4 in conversation