Proc sql issue

[LearnByMistk]

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 .

[RW9 · Accepted solution]

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;

[LearnByMistk]

Ok let me check on my given data than i revert but really thanks for so prompt reply ..

[AskoLötjönen]

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;

[Ksharp]

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

[Steelers_In_DC]

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);

[LearnByMistk]

Thanks all of you for your help * Support  ..that's the only reason i love this community