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
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;
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
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
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.