topic Re: Summary with spaces in SAS Programming

Summary with spaces

Lhbernagozzi — Fri, 23 Feb 2018 12:54:35 GMT

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.

Re: Summary with spaces

Astounding — Fri, 23 Feb 2018 14:21:35 GMT

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.

Re: Summary with spaces

novinosrin — Fri, 23 Feb 2018 21:43:42 GMT

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;

Re: Summary with spaces

Lhbernagozzi — Mon, 26 Feb 2018 11:55:11 GMT

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