Desktop productivity for business analysts and programmers

proc sql with summary function

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

proc sql with summary function

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;


Accepted Solutions
Solution
‎03-27-2014 12:02 PM
New Contributor
Posts: 2

Re: proc sql with summary function

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;

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: proc sql with summary function

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.

N/A
Posts: 1

Re: proc sql with summary function

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;

Solution
‎03-27-2014 12:02 PM
New Contributor
Posts: 2

Re: proc sql with summary function

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;

Contributor
Posts: 55

Re: proc sql with summary function

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

New Contributor
Posts: 2

Re: proc sql with summary function

Glad to help

Contributor
Posts: 55

Re: proc sql with summary function

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 436 views
  • 0 likes
  • 4 in conversation