Calcite | Level 5

## Summary with spaces

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

1 ACCEPTED SOLUTION

Accepted Solutions
PROC Star

## Re: Summary with spaces

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.

3 REPLIES 3
PROC Star

## Re: Summary with spaces

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.

Calcite | Level 5

## Re: Summary with spaces

Thank so much. @Astounding

I have nerver used the function Dif before but now I believe I'll use a lot.

Sas is amazing.

Thanks

Tourmaline | Level 20

## Re: Summary with spaces

``````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;``````
Discussion stats
• 3 replies
• 844 views
• 0 likes
• 3 in conversation