topic SQL Count in SAS Programming

SQL Count

WilliamB — Thu, 12 Sep 2019 08:33:54 GMT

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

Re: SQL Count

PeterClemmensen — Thu, 12 Sep 2019 08:52:34 GMT

How come NB_Port=1 for Name=RTY?

Re: SQL Count

gamotte — Thu, 12 Sep 2019 10:09:51 GMT

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.

Re: SQL Count

Kurt_Bremser — Thu, 12 Sep 2019 08:56:48 GMT

And DGT = 2?

Re: SQL Count

PeterClemmensen — Thu, 12 Sep 2019 09:13:52 GMT

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;

Re: SQL Count

SwissC — Thu, 12 Sep 2019 09:43:58 GMT

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;

Re: SQL Count

Ksharp — Thu, 12 Sep 2019 11:04:33 GMT

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;