topic Re: Equivalent in proc sql in SAS Procedures

Equivalent in proc sql

mansour_ib_sas — Mon, 19 Nov 2018 18:00:43 GMT

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

Re: Equivalent in proc sql

novinosrin — Mon, 19 Nov 2018 18:39:15 GMT

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

Re: Equivalent in proc sql

PGStats — Mon, 19 Nov 2018 19:11:07 GMT

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;