Hi ,
i need to figure out few steps with in a same or 2 queries
data i have
drp_cnt blk_cnt cell site
23 43 A
132 34 B
154 63 C
So i want to figure out average of all drop_cnt as (103) and blk_cnt as (46.67) .. this average is not depending on cell site but a genral average of whole count (drop n block).
data want
drp_cnt blk_cnt cell site drp_flg blk_flg
23 43 A 1 1
132 34 B 0 1
154 63 C 0 0
Here drp_flg and blk_flg ...i create depending upon the average values of drop n blk count ie drop_cnt as (103) and blk_cnt as (46.67)...so if value is > avg its 0 else 1 .
I tried to resolve this using calculated staement in proc sql but dont get the required result .
Your help is greatly appreciated .
Something like:
data have;
drp_cnt=23; blk_cnt=43; cell="A"; output;
drp_cnt=132; blk_cnt=34; cell="B"; output;
drp_cnt=154; blk_cnt=63; cell="C"; output;
run;
proc sql;
create table WANT as
select A.*,
case when B.AVG1 > A.DRP_CNT then 1 else 0 end as DRP_FLG,
case when B.AVG2 > A.BLK_CNT then 1 else 0 end as BLK_FLG
from HAVE A
left join (select AVG(DRP_CNT) as AVG1, AVG(BLK_CNT) as AVG2 from HAVE) B
on 1=1; /* Note, there isn't a grouping you have given, so I just merge the result value to everything. This isn't advised, but with the small example should work */
quit;
Something like:
data have;
drp_cnt=23; blk_cnt=43; cell="A"; output;
drp_cnt=132; blk_cnt=34; cell="B"; output;
drp_cnt=154; blk_cnt=63; cell="C"; output;
run;
proc sql;
create table WANT as
select A.*,
case when B.AVG1 > A.DRP_CNT then 1 else 0 end as DRP_FLG,
case when B.AVG2 > A.BLK_CNT then 1 else 0 end as BLK_FLG
from HAVE A
left join (select AVG(DRP_CNT) as AVG1, AVG(BLK_CNT) as AVG2 from HAVE) B
on 1=1; /* Note, there isn't a grouping you have given, so I just merge the result value to everything. This isn't advised, but with the small example should work */
quit;
Ok let me check on my given data than i revert but really thanks for so prompt reply ..
proc sql;
create table want as
select drp_cnt,
blk_cnt,
cell_site,
case when drp_cnt < mean(drp_cnt) then 1
when drp_cnt > mean(drp_cnt) then 0 end as drp_flg,
case when blk_cnt < mean(blk_cnt) then 1
when blk_cnt > mean(blk_cnt) then 0 end as blk_flg
from have;
;
quit;
data have; input drp_cnt blk_cnt cell_site $; cards; 23 43 A 132 34 B 154 63 C ; run; proc sql; create table want(drop=m1 m2) as select * ,mean(drp_cnt) as m1,mean(blk_cnt) as m2,(drp_cnt lt calculated m1) as f1,(blk_cnt lt calculated m2) as f2 from have; quit;
Xia Keshan
Here's a shout out to Black Flag:
data have;
infile cards dsd dlm=',';
input drp_cnt blk_cnt cell_site $;
cards;
23,43,A
132,34,B
154,63,C
;
run;
proc sql;
create table want as
select *,
case
when blk_cnt > avg_blk_cnt then 0
else 1
end as blk_flg,
case
when drp_cnt > avg_drp_cnt then 0
else 1
end as drp_flg from(
select *,avg(drp_cnt) as avg_drp_cnt,avg(blk_cnt) as avg_blk_cnt format=number7.2
from have);
Thanks all of you for your help * Support ..that's the only reason i love this community
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.