SAS Sub Ranking

[cmoore]

Hi,

What would be the best way to transform the data below;

Variable	Firm A	Firm B	Firm C
Africa_wide1	Banking & Finance (Band 1)	Corporate/M&A (Band 3)	Banking & Finance (Band 1)
Africa_wide2	Corporate/Commercial: OHADA Specialists (Band 1)	Dispute Resolution (Band 2)	Corporate/M&A (Band 1)
Africa_wide3	Corporate/M&A (Band 1)	Projects & Energy (Band 3)	Dispute Resolution (Band 2)
Africa_wide4	Dispute Resolution (Band 2)	Projects & Energy: Mining & Minerals (Band 3)	Projects & Energy (Band 1)
Africa_wide5	Projects & Energy (Band 1)		Projects & Energy: Mining & Minerals (Band 2)
Africa_wide6	Projects & Energy: Mining & Minerals (Band 2)
Africa_wide7	TMT (Band 3)

into the following data set;

Variable	Firm A	Firm B	Firm C
Africa_wide1	Banking & Finance (Band 1)		Banking & Finance (Band 1)
Africa_wide2	Corporate/Commercial: OHADA Specialists (Band 1)
Africa_wide3	Corporate/M&A (Band 1)	Corporate/M&A (Band 3)	Corporate/M&A (Band 1)
Africa_wide4	Dispute Resolution (Band 2)	Dispute Resolution (Band 2)	Dispute Resolution (Band 2)
Africa_wide5	Projects & Energy (Band 1)	Projects & Energy (Band 3)	Projects & Energy (Band 1)
Africa_wide6	Projects & Energy: Mining & Minerals (Band 2)	Projects & Energy: Mining & Minerals (Band 3)	Projects & Energy: Mining & Minerals (Band 2)
Africa_wide7	TMT (Band 3)

Basically I am trying to line up the variables in the correct order across each different firm flag. Some firm flags don't have all of the variables contained within them but I still want them in alphabetical order ascending. There will be more variables to add such as Europe_wide1 to Europe_wide5, for example. Please could you advise on the best way to do this?

Thanks

Chris

[collinelliot]

I think you want this?

proc sql;
SELECT a.variable, a.firm_a, b.firm_b, c.firm_c
FROM have AS a
LEFT JOIN have AS b
ON a.firm_a = b.firm_a
LEFT JOIN have AS c
ON a.firm_a = c.firm_c;
quit;

[novinosrin]

Hi, 

/*have is your sample*/

data temp;
set have;
array firm(*) firm_a firm_b firm_c;
array firm1(*) $75 _firm_a _firm_b _firm_c;
do _n_=1 to dim(firm);
firm1(_n_)=compress(firm(_n_),'0123456789');
end;
run;

/*want is your result dataset*/
data want;
if (_n_ = 1) then do;
if 0 then set temp;
declare hash myhash(dataset: "temp(keep=_firm_b firm_b");
rc = myhash.definekey('_firm_b');
myhash.definedata('firm_b');
myhash.definedone();
declare hash myhash2(dataset: "temp(keep=_firm_c firm_c");
rc = myhash2.definekey('_firm_c');
myhash2.definedata('firm_c');
myhash2.definedone();
end;
set temp(keep=variable firm_a _firm_a);
if myhash.find(key:_firm_a) ne 0 then call missing(firm_b);
if myhash2.find(key:_firm_a) ne 0 then call missing(firm_c);
drop rc _:;
run;

Regards,

Naveen Srinivasan

[art297]

I think that the following will do what you want:

proc sql;
  create table want as
    SELECT a.variable, a.firm_a, b.firm_b, c.firm_c
      FROM have AS a
        LEFT JOIN have AS b
          ON scan(a.firm_a,1,'(') = scan(b.firm_b,1,'(')
            LEFT JOIN have AS c
              ON scan(a.firm_a,1,'(') = scan(c.firm_c,1,'(')
  ;
quit;

Art, CEO, AnalystFinder.com