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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.