Hello everyone, I have the following tables: /* Tabela Artigos */
proc sql;
create table artigos
(REFERENCIA char(1),
ID_DPTO num,
COD_FAMILIA char(3),
DATA_ALTA num informat=DDMMYY10.
format=DDMMYY10.
);
insert into artigos
values('A',1,'1F1','21FEB10'd)
values('B',2,'2F1','07FEB14'd)
values('C',2,'2F1','15MAY14'd)
values('D',2,'2F1','01OCT05'd)
values('E',2,'2F1','31OCT10'd)
values('F',3,'3F1','20APR01'd)
values('G',3,'3F2','14FEB15'd)
values('H',4,'4F1','25MAR09'd);
quit;
/* Tabela Campanhas */
proc sql;
create table campanhas
(ID_CAMPANHA char(3),
TIPO char(20),
DATA_CAMP num informat=DDMMYY10.
format=DDMMYY10.
);
insert into campanhas
values('CA1','VENDA','05FEB12'd)
values('CA2','VENDA','04FEB12'd)
values('CA3','PÓS-VENDA','02OCT09'd)
values('CA4','VENDA','06MAY10'd)
values('CA5','PÓS-VENDA','30NOV10'd)
values('CA6','PÓS-VENDA','07FEB15'd);
quit;
/* Tabela Departamento Campanha */
proc sql;
create table depto_campanha
(ID_DPTO num,
ID_CAMPANHA char(3)
);
insert into depto_campanha
values(1,'CA1')
values(1,'CA4')
values(2,'CA2')
values(3,'CA2')
values(3,'CA5')
values(3,'CA6')
values(4,'CA1')
values(4,'CA2')
values(4,'CA3')
values(4,'CA6');
quit;
/* Tabela Vendas */
proc sql;
create table vendas
(TALAO num informat=z2.
format=z2.,
REFERENCIA char(1),
PRECO num,
DATA_VENDA num informat=DDMMYY10.
format=DDMMYY10.
);
insert into vendas
values(01,'A',15,'11FEB12'd)
values(02,'A',18,'15FEB12'd)
values(03,'A',14,'02OCT13'd)
values(04,'D',37.95,'06AUG12'd)
values(05,'E',125.95,'30NOV10'd)
values(06,'E',150,'05FEB11'd)
values(07,'H',22.99,'11APR10'd)
values(08,'H',24,'08AUG11'd);
quit; I need to create a query (only one and in sql) in which, for each campaign of the "VENDA" type (column "TIPO"), i obtain the average value of beads (column "TALAO"), the number of purchased references (column "REFERENCIAS") and the number of beads ("TALAO") with sales (column "PRECO") higher than the average value of the receipt. I already could accomplish part of it but in my query i don't obtai the last part "number of beads with sales higher than the average value of the receipt". Can anyone help me here? My solution so far: proc sql;
select
t1.id_campanha
,t1.tipo
,avg(t4.preco) as avg_price_beads
,count(distinct t3.referencia) as nr_referencies
,count(distinct t4.talao) as nr_beads
from campanhas t1
left join depto_campanha t2 on t1.id_campanha=t2.id_campanha
left join artigos t3 on t2.id_dpto=t3.id_dpto
inner join vendas t4 on t4.referencia=t3.referencia
where t1.tipo="VENDA"
group by 1,2
having t4.preco > avg_price_beads ;
quit; I also tried: proc sql;
select
t1.id_campanha
,t1.tipo
,avg(t4.preco) as avg_price_beads
,count(distinct t3.referencia) as nr_referencies
,count(distinct case when t4.preco > calculated avg_price_beads then t4.talao end ) as nr_beads
from campanhas t1
left join depto_campanha t2 on t1.id_campanha=t2.id_campanha
left join artigos t3 on t2.id_dpto=t3.id_dpto
inner join vendas t4 on t4.referencia=t3.referencia
where t1.tipo="VENDA"
group by 1,2
;
quit;
... View more