Hello, I would like to count the number of ports of each individual in Table B to add the information in Table A.
Thanks for your help
Table A | Table B | |||
Name | LoC | Name | Port | |
BARU | Z | BARU | AS | |
RTY | a | BARU | AG | |
DGT | v | DGT | DS | |
ETOM | q | BARU | QS | |
RTY | AQ | |||
RTY | QR | |||
ETOM | AR | |||
ETOM | AV | |||
Want | ||||
Name | LoC | NB_Port | ||
BARU | z | 3 | ||
RTY | a | 1 | ||
DGT | v | 2 | ||
ETOM | q | 2 |
How come NB_Port=1 for Name=RTY?
Hello,
Not tested :
proc sql noprint;
CREATE TABLE want AS
SELECT a.*, count(b.PORT)
FROM a
LEFT JOIN b
ON strip(upcase(b.Name))=strip(upcase(a.NAME));
quit;
According to the desired output, you may want to use count(DISTINCT PORT).
Edit : Forgot to group by Name. See @SwissC's answer instead.
data TableA;
input Name $ LoC $;
datalines;
BARU Z
RTY a
DGT v
ETOM q
;
data TableB;
input Name $ Port $;
datalines;
BARU AS
BARU AG
DGT DS
BARU QS
RTY AQ
RTY QR
ETOM AR
ETOM AV
;
data want(drop=rc);
if _N_=1 then do;
dcl hash h();
h.definekey('Name');
h.definedata('NB_Port');
h.definedone();
do until (lr);
set TableB(keep=Name) end=lr;
if h.find()=0 then NB_Port+1;
else NB_Port=1;
h.replace();
end;
end;
set TableA;
NB_Port=.;
rc=h.find();
run;
proc sql noprint;
CREATE TABLE want AS
SELECT a.*, nb_port
FROM have1 a
LEFT JOIN (select name, count(distinct(port)) as nb_port from have2 group by name) b
ON strip(upcase(b.Name))=strip(upcase(a.NAME));
quit;
data TableA;
input Name $ LoC $;
datalines;
BARU Z
RTY a
DGT v
ETOM q
;
data TableB;
input Name $ Port $;
datalines;
BARU AS
BARU AG
DGT DS
BARU QS
RTY AQ
RTY QR
ETOM AR
ETOM AV
;
proc sql;
create table want as
select a.name,a.loc,count(*) as n
from tablea as a left join tableb as b
on a.name=b.name
group by a.name,a.loc ;
quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.