topic Re: proc sql with summary function in SAS Enterprise Guide

proc sql with summary function

jimksas — Wed, 26 Mar 2014 22:47:50 GMT

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;

Re: proc sql with summary function

RW9 — Thu, 27 Mar 2014 15:22:14 GMT

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.

Re: proc sql with summary function

murrak6 — Thu, 27 Mar 2014 15:37:21 GMT

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;

Re: proc sql with summary function

amnist — Thu, 27 Mar 2014 16:02:56 GMT

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;

Re: proc sql with summary function

jimksas — Thu, 27 Mar 2014 16:48:57 GMT

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

Re: proc sql with summary function

amnist — Thu, 27 Mar 2014 17:31:52 GMT

Glad to help

Re: proc sql with summary function

jimksas — Thu, 27 Mar 2014 18:20:22 GMT

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