DATA Step, Macro, Functions and more

SAS Sub Ranking

Reply
Contributor
Posts: 32

SAS Sub Ranking

Hi,

 

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

 

VariableFirm AFirm BFirm C
Africa_wide1Banking & Finance (Band 1)Corporate/M&A (Band 3)Banking & Finance (Band 1)
Africa_wide2Corporate/Commercial: OHADA Specialists (Band 1)Dispute Resolution (Band 2)Corporate/M&A (Band 1)
Africa_wide3Corporate/M&A (Band 1)Projects & Energy (Band 3)Dispute Resolution (Band 2)
Africa_wide4Dispute Resolution (Band 2)Projects & Energy: Mining & Minerals (Band 3)Projects & Energy (Band 1)
Africa_wide5Projects & Energy (Band 1) Projects & Energy: Mining & Minerals (Band 2)
Africa_wide6Projects & Energy: Mining & Minerals (Band 2)  
Africa_wide7TMT (Band 3)  

 

into the following data set;

 

VariableFirm AFirm BFirm C
Africa_wide1Banking & Finance (Band 1) Banking & Finance (Band 1)
Africa_wide2Corporate/Commercial: OHADA Specialists (Band 1)  
Africa_wide3Corporate/M&A (Band 1)Corporate/M&A (Band 3)Corporate/M&A (Band 1)
Africa_wide4Dispute Resolution (Band 2)Dispute Resolution (Band 2)Dispute Resolution (Band 2)
Africa_wide5Projects & Energy (Band 1)Projects & Energy (Band 3)Projects & Energy (Band 1)
Africa_wide6Projects & Energy: Mining & Minerals (Band 2)Projects & Energy: Mining & Minerals (Band 3)Projects & Energy: Mining & Minerals (Band 2)
Africa_wide7TMT (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

PROC Star
Posts: 288

Re: SAS Sub Ranking

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;
PROC Star
Posts: 172

Re: SAS Sub Ranking

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

PROC Star
Posts: 7,363

Re: SAS Sub Ranking

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

Ask a Question
Discussion stats
  • 3 replies
  • 151 views
  • 0 likes
  • 4 in conversation