- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi, I'm new in Sas and I don't have a clue of how to do one step of my program. Can you, please, give me some help?
I need count and take avg from my stock, but only in the days I had the item in stock.
Base Table.
Date | Item | Qty Stock |
01FEB2018 | T-shirt1 | 2 |
02FEB2018 | T-shirt1 | 3 |
03FEB2018 | T-shirt1 | 3 |
04FEB2018 | T-shirt1 | 38 |
05FEB2018 | T-shirt1 | 38 |
06FEB2018 | T-shirt1 | 2 |
07FEB2018 | T-shirt1 | 2 |
08FEB2018 | T-shirt1 | 2 |
09FEB2018 | T-shirt1 | 1 |
10FEB2018 | T-shirt1 | 2 |
15FEB2018 | T-shirt1 | 1 |
16FEB2018 | T-shirt1 | 1 |
17FEB2018 | T-shirt1 | 1 |
18FEB2018 | T-shirt1 | 72 |
19FEB2018 | T-shirt1 | 68 |
20FEB2018 | T-shirt1 | 69 |
The variables DateBegin and DateEnd is not necessary. I put in the result table to be more to figure what I need.
Result Table
Item | DateBegin | DateEnd | CountDays | AvgStock |
T-shirt1 | 01FEB2018 | 10FEB2018 | 10 | 9,3 |
T-shirt1 | 15FEB2018 | 20FEB2018 | 6 | 35,3 |
Thanks in advanced.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I can't tell whether you need to do this for just one item (T-shirt1) or many items. So here's a program that would work in either case.
proc sort data=have;
by item date;
run;
data groups;
set have;
by item;
days = dif(date);
if first.item or days > 1 then group + 1;
drop days;
run;
This gives you a new variable GROUP that is unique for each set of observations that should be summarized separately from the other observations.
Then summarize. I'll keep the dates in there to match your result.
proc summary data=groups;
by item group;
var date qty;
output out=want (drop=_type_ group rename=(_freq_=CountDays))
min(date) = DateBegin max(date) = DateEnd mean(qty)=AvgStock;
run;
When printing, you probably want to apply a format to DateBegin and DateEnd, such as:
format datebegin dateend date9.;
This is untested code, so it might require slight tweaking.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I can't tell whether you need to do this for just one item (T-shirt1) or many items. So here's a program that would work in either case.
proc sort data=have;
by item date;
run;
data groups;
set have;
by item;
days = dif(date);
if first.item or days > 1 then group + 1;
drop days;
run;
This gives you a new variable GROUP that is unique for each set of observations that should be summarized separately from the other observations.
Then summarize. I'll keep the dates in there to match your result.
proc summary data=groups;
by item group;
var date qty;
output out=want (drop=_type_ group rename=(_freq_=CountDays))
min(date) = DateBegin max(date) = DateEnd mean(qty)=AvgStock;
run;
When printing, you probably want to apply a format to DateBegin and DateEnd, such as:
format datebegin dateend date9.;
This is untested code, so it might require slight tweaking.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank so much. @Astounding
Your code solve the problem.
I have nerver used the function Dif before but now I believe I'll use a lot.
Sas is amazing.
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input Date :date9. Item : $10. QtyStock;
format date date9.;
datalines;
01FEB2018 T-shirt1 2
02FEB2018 T-shirt1 3
03FEB2018 T-shirt1 3
04FEB2018 T-shirt1 38
05FEB2018 T-shirt1 38
06FEB2018 T-shirt1 2
07FEB2018 T-shirt1 2
08FEB2018 T-shirt1 2
09FEB2018 T-shirt1 1
10FEB2018 T-shirt1 2
15FEB2018 T-shirt1 1
16FEB2018 T-shirt1 1
17FEB2018 T-shirt1 1
18FEB2018 T-shirt1 72
19FEB2018 T-shirt1 68
20FEB2018 T-shirt1 69
;
data want;
set have;
by item date;
array t(1000) _temporary_;
if first.item then call missing(of t(*));
days = dif(date);
if days ne 1 then count=1;
else if days=1 then count+1;
_c=lag(count);
if not first.item and count=1 then do;
countdays=_c;
avgstock=sum(of t(*))/_c;
output;
call missing(of t(*));
end;
else if last.item then do;
t(count)=qtystock;
countdays=count;
avgstock=sum(of t(*))/count;
output;
end;
t(count)=qtystock;
keep item countdays avgstock;
run;