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;
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;
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.
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;
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;
Hi Amnist - it worked fine - thanks lot!!!
Glad to help
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!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.