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 |
How is Sum_Connexion=5 for Clients='Thomas'?
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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.