BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lhbernagozzi
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
Astounding
PROC Star

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.

Lhbernagozzi
Calcite | Level 5

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

novinosrin
Tourmaline | Level 20
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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