proc sql with summary function

[jimksas]

Hello friends i am another proc sql query if someone can help please...

i have variables like,

date

store_no

item_no

sale_qty

onhand_qty

i want to see unique ItemNbr per store  +   sale_qty onhand_qty for that item_no on particular date

so may be my outcome would be like,

date               store_no         item_no        sale_qty       onhand_qty

--------------------------------------------------------------------------------------------------------

26mar14         123456             90055678         12                100

                                             90065656           0                10

                                             90078785         10                 50

                                             78528521           9                   1

                                             78585522           2                 35

I am using following logic but not working properly...

proc sql;

create table a as select date, item_no, count(item_no) as item_no_cnt, sum(sale_qty) as sale_qty_sum,

sum(onhand_qty) as onhand_qty_sum

from testdataset

where date='26mar14'd

group by item_no

having item_no_cnt>-1

order by item_no_cnt desc;

quit;

[RW9]

Hi,

Maybe break your logic down into smaller parts.

proc sql;

  create table ... as

  select  COALESCE(A.DATE,B.DATE) as DATE,

          COALESCE(A.STORE_NO,B.STORE_NO) as STORE_NO,

          COALESCE(A.ITEM_NO,B.ITEM_NO) as ITEM_NO,

          A.COUNT_OF_ITEM_NO,

          B.SUM_OF_SALE_QTY

  from    (select DATE,STORE_NO,ITEM_NO,COUNT(ITEM_NO) as COUNT_OF_ITEM_NO from ... group by DATE,STORE_NO) A

  full join (select DATE,STORE_NO,ITEM_NO,SUM(SALE_QTY) as SUM_OF_SALE_QTY from ... group by DATE,STORE_NO) B

  on      A.DATE=B.DATE

  and     A.STORE_NO=B.STORE_NO

  and     A.ITEM_NO=B.ITEM_NO;

quit;

So in the above, doing the sum in a subquery and the count in a subquery, then join them together.

[murrak6]

Try:

proc sql;

create table a as select date, item_no, count(*) as item_no_cnt, sum(sale_qty) as sale_qty_sum,

sum(onhand_qty) as onhand_qty_sum

from testdataset

where date='26mar14'd

group by item_no

having item_no_cnt>-1

order by item_no_cnt desc;

quit;

[amnist · Accepted solution]

You did not include date in the grouping clause. Please try this:

proc sql;

create table a as select date, item_no, count(*) as item_no_cnt, sum(sale_qty) as sale_qty_sum,

sum(onhand_qty) as onhand_qty_sum

from testdataset

where date='26mar14'd

group by date,item_no

having item_no_cnt>-1

order by item_no_cnt desc;

quit;

[jimksas]

Hi Amnist - it worked fine - thanks lot!!!

[amnist]

Glad to help

[jimksas]

Amnist - i think we are missing "store_no" column here in this query- i just realize it...:-)

i want everything by "store_no" then "item_no" - exactly as i have shown in my first question thread...

thanks!!!