Solved
Contributor
Posts: 29

# Proc sql issue

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 .

Accepted Solutions
Solution
‎05-20-2015 08:00 AM
Super User
Posts: 9,617

## Re: Proc sql issue

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;

All Replies
Solution
‎05-20-2015 08:00 AM
Super User
Posts: 9,617

## Re: Proc sql issue

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;

Contributor
Posts: 29

## Re: Proc sql issue

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

Contributor
Posts: 44

## Re: Proc sql issue

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;

Super User
Posts: 10,788

## Re: Proc sql issue

```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

Valued Guide
Posts: 864

## Re: Proc sql issue

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

Contributor
Posts: 29