topic Re: Join data / IF in SAS Programming

Join data / IF

WilliamB — Thu, 07 Nov 2019 13:42:52 GMT

Hello,

I want to gather tables, adding up data, such as the number of "months" and the number of "connections".

But I want to create an "ACTIVE" variable.
If "Sum Month" is greater than or equal to Connect then Active = "Yes" otherwise "No".

Thanks a lot for your help.

Table 1				Table 2
Clients	Age
Thomas	10			Clients	Month	Connexion
Nelson	14			Thomas	30SET2019	1
Xavier	15			Thomas	31OCT2019	0
Florine	15			Nelson	31OCT2019	2
				Xavier	31JUL2019	1
				Xavier	31AUG2019	2
				Xavier	30SET2019	0
				Xavier	31OCT2019	1
				Xavier	31OCT2019	1
				Florine	31JUL2019	1
				Florine	31AUG2019	0
				Florine	30SET2019	0
				Florine	31OCT2019	0
				Florine	31OCT2019	0


Want

Clients	Age	Sum Month	Sum Connexion	Actif
Thomas	10	2	1	No
Nelson	14	1	2	Yes
Xavier	15	5	5	Yes
Florine	15	5	1	No

Re: Join data / IF

PeterClemmensen — Thu, 07 Nov 2019 13:31:15 GMT

How is Sum_Connexion=5 for Clients='Thomas'?

Re: Join data / IF

WilliamB — Thu, 07 Nov 2019 13:43:19 GMT

It's a mistake it's 1.

Re: Join data / IF

PeterClemmensen — Thu, 07 Nov 2019 14:06:05 GMT

I think you want to do something like this

data Table1;
input Clients $ Age;
datalines; 
Thomas 10 
Nelson 14 
Xavier 15 
Florine 15
;

data Table2;
input Clients $ Month :date9. Connexion;
format Month date9.;
datalines;
Thomas 30SEP2019 1
Thomas 31OCT2019 0
Nelson 31OCT2019 2
Xavier 31JUL2019 1
Xavier 31AUG2019 2
Xavier 30SEP2019 0
Xavier 31OCT2019 1
Xavier 31OCT2019 1
Florine 31JUL2019 1
Florine 31AUG2019 0
Florine 30SEP2019 0
Florine 31OCT2019 0
Florine 31OCT2019 0
;

proc sql;
    create table want as
    select a.Clients,
           a.Age,
           n(Month) as SumMonth,
           sum(Connexion) as SumConnexion,
           ifc(n(Month) >= sum(Connexion), 'Yes', 'No') as Actif
    from Table1 as a left join Table2 as b
    on a.Clients=b.Clients
    group by a.Clients, a.Age;
quit;

Result:

Clients   Age   SumMonth   SumConnexion   Actif
Florine   15    5           1             Yes
Nelson    14    1           2             No
Thomas    10    2           1             Yes
Xavier    15    5           5             Yes

Re: Join data / IF

novinosrin — Thu, 07 Nov 2019 14:32:11 GMT

One to many or Many to one plus an ordered dataset(as sample suggests) is super fun for BY GROUP processing

Hi @WilliamB Please review the Active variable result in your sample and the conditional logic. Nonetheless, that's minor and I am sure you will adjust quite easily


data Table1;
input Clients $ Age;
datalines; 
Thomas 10 
Nelson 14 
Xavier 15 
Florine 15
;

data Table2;
input Clients $ Month :date9. Connexion;
format Month date9.;
datalines;
Thomas 30SEP2019 1
Thomas 31OCT2019 0
Nelson 31OCT2019 2
Xavier 31JUL2019 1
Xavier 31AUG2019 2
Xavier 30SEP2019 0
Xavier 31OCT2019 1
Xavier 31OCT2019 1
Florine 31JUL2019 1
Florine 31AUG2019 0
Florine 30SEP2019 0
Florine 31OCT2019 0
Florine 31OCT2019 0
;

data want;
if _n_=1 then do;
 if 0 then set table1;/*just to maintain order*/
  dcl hash H (dataset:'table1') ;
   h.definekey  ("clients") ;
   h.definedata ("age") ;
   h.definedone () ;
 end;
 do sum_month=1 by 1 until(last.clients);
  set table2;
  by clients notsorted;
  if h.find() ne 0 then continue;
  sum_connexion=sum(sum_connexion,Connexion);
 end;
 length Active $3;
 if sum_month>=sum_connexion then Active="Yes";
 else Active="No";
 keep clients age sum_: active;
run;