Hello,
How can I get the equivalent of this solution in sql?
data test;
input
commune $ etablissement $ effectif_etablissement $ identifiant it1 it2 it3 it4;
cards;
971BA Mau 20 1 9 2 2 2
971BA Mau 20 2 0 1 9 9
971BA Cham 22 12 1 9 2 0
971BA Cham 22 14 2 1 9 0
971STC Issa 23 27 1 1 9 9
971STC Issa 23 31 9 0 9 9
971VFO Ving 23 54 1 9 0 0
971VFO Ving 23 58 1 2 1 1
971GOU Dora 20 72 1 1 1 9
971GOU Dora 20 77 1 1 9 0
;
run;
proc freq data=test(keep=etablissement it1) noprint ;
tables etablissement*it1 / out=freq_it1 (drop=percent) sparse;
run ;
thank You
proc sql;
create table freq_it1 as
select a.etablissement, b.it1, count(c.it1) as COUNT
from
(select distinct etablissement from test) as a cross join
(select distinct it1 from test) as b left join
test as c on a.etablissement=c.etablissement and b.it1=c.it1
group by a.etablissement, b.it1;
quit;
data test;
input
commune $ etablissement $ effectif_etablissement $ identifiant it1 it2 it3 it4;
cards;
971BA Mau 20 1 9 2 2 2
971BA Mau 20 2 0 1 9 9
971BA Cham 22 12 1 9 2 0
971BA Cham 22 14 2 1 9 0
971STC Issa 23 27 1 1 9 9
971STC Issa 23 31 9 0 9 9
971VFO Ving 23 54 1 9 0 0
971VFO Ving 23 58 1 2 1 1
971GOU Dora 20 72 1 1 1 9
971GOU Dora 20 77 1 1 9 0
;
run;
proc sql;
create table x as
select etablissement,it1
from ((select distinct etablissement from test ),(select distinct it1 from test ));
quit;
proc sql;
create table want as
select a.etablissement,a.it1,count(b.it1) as freq
from x a left join test b
on a.etablissement=b.etablissement and a.it1=b.it1
group by a.etablissement,a.it1;
quit;
But why the tedious process, although the task makes me learn regardless
proc sql;
create table freq_it1 as
select a.etablissement, b.it1, count(c.it1) as COUNT
from
(select distinct etablissement from test) as a cross join
(select distinct it1 from test) as b left join
test as c on a.etablissement=c.etablissement and b.it1=c.it1
group by a.etablissement, b.it1;
quit;
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.