topic Re: Proc sql issue in SAS Procedures

Proc sql issue

LearnByMistk — Wed, 20 May 2015 11:51:57 GMT

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 .

Re: Proc sql issue

RW9 — Wed, 20 May 2015 12:00:02 GMT

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;

Re: Proc sql issue

LearnByMistk — Wed, 20 May 2015 12:01:50 GMT

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

Re: Proc sql issue

AskoLötjönen — Wed, 20 May 2015 12:07:04 GMT

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;

Re: Proc sql issue

Ksharp — Wed, 20 May 2015 12:32:38 GMT

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

Re: Proc sql issue

Steelers_In_DC — Wed, 20 May 2015 12:36:51 GMT

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

Re: Proc sql issue

LearnByMistk — Wed, 20 May 2015 15:09:47 GMT

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