DATA Step, Macro, Functions and more

Summary with spaces

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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.

DateItemQty Stock
01FEB2018T-shirt12
02FEB2018T-shirt13
03FEB2018T-shirt13
04FEB2018T-shirt138
05FEB2018T-shirt138
06FEB2018T-shirt12
07FEB2018T-shirt12
08FEB2018T-shirt12
09FEB2018T-shirt11
10FEB2018T-shirt12
15FEB2018T-shirt11
16FEB2018T-shirt11
17FEB2018T-shirt11
18FEB2018T-shirt172
19FEB2018T-shirt168
20FEB2018T-shirt169

 

The variables DateBegin and DateEnd is not necessary. I put in the result table to be more to figure what I need.

 

Result Table

ItemDateBeginDateEndCountDaysAvgStock
T-shirt101FEB201810FEB2018109,3
T-shirt115FEB201820FEB2018635,3


Thanks in advanced. 


Accepted Solutions
Solution
‎02-26-2018 06:50 AM
Super User
Posts: 6,644

Re: Summary with spaces

Posted in reply to Lhbernagozzi

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.

View solution in original post


All Replies
Solution
‎02-26-2018 06:50 AM
Super User
Posts: 6,644

Re: Summary with spaces

Posted in reply to Lhbernagozzi

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.

New Contributor
Posts: 2

Re: Summary with spaces

Posted in reply to Astounding

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

PROC Star
Posts: 1,605

Re: Summary with spaces

Posted in reply to Lhbernagozzi
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;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 124 views
  • 0 likes
  • 3 in conversation