BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

data test;

infile datalines;

input Year $ Month $ Dept1N Dept2N ;

return;

datalines;

2014 1Jun2014 2500 2100

2014 1Jul2014 2330 2220

2014 1Aug2014 1500 2140

2014 1Sep2014 2500 2670

2014 1Oct2014 4500 4100

2014 1Nov2014 2600 7100

;

run;

Output

Year Month Dept1N Dept2N
2014 1Jun2014 2500 2100
2014 1Jul2014 2330 2220
2014 1Aug2014 1500 2140
2014 1Sep2014 2500 2670
2014 1Oct2014 4500 4100
2014 1Nov2014 2600 7100

I need to add a 3 month Avg column based on the following

Dept1N(sum of the 1st 3 rows, then 2nd 3 rows...etc.  Example 2500+2330+1500=6330)

Dept2N(2100+2220+2140=6460)

Add Dept1N+Dept2N (6330+6460=12790

To get my 3 month avg 6460/12790 = 50.50

Can this be best handles in proc report or here in the datastep and how??

Thus the calculation needs to evaluate every 3 months

3 REPLIES 3
Reeza
Super User

In a data step use the LAG function if you are sure you have no missing months. If you have missing months, a SQL query or PROC EXPAND are your best option.

 

Do you have missing months that you need to deal with?

If no,then the following is probably the quickest method:

 

average3 = currentVar + lag(currentVar) + lag2(currentVar);

 

EDIT: I suppose if this is an average you should divide by 3 or use the MEAN function.

 

average3 = mean(curentVar, lag1(currentVar), lag2(currentVar));

 

Rwon
Obsidian | Level 7

If my understanding of your question is correct, this should get you 80% of the way there. I don't understand the examples you gave. You listed the 3 month sum for department 2 divided by the 3 month sum for both departments and called it average? I believe this calculation is the percentage of department 2's three-month sales. Anyway, here is the code to append the static 3 month averages to the base file:

 

data test;
infile datalines;
input Year $ Month $ Dept1N Dept2N ;
return;
datalines;
2014 1Jun2014 2500 2100
2014 1Jul2014 2330 2220
2014 1Aug2014 1500 2140
2014 1Sep2014 2500 2670
2014 1Oct2014 4500 4100
2014 1Nov2014 2600 7100
;
run;

*Create identifiers for each 3 month segment;
data want;
set test;
three_month_count=ceil(_n_/3);
run;

*Calculate static three month averages for each department;
proc summary data = want nway nmiss;
class three_month_count;
var Dept1N Dept2N;
output out = want2 ( drop = _type_ _freq_ ) mean = avg_Dept1N avg_Dept2N;
run;

*Merge the three month averages back to the base file;
data append_averages;
merge want want2;
by three_month_count;
*Add any calculations you need here; run;
kiranv_
Rhodochrosite | Level 12

Best way would be do by @Reeza suggestion.

But yu can achieve same by proc sql as shown below

proc sql;
select *,
sum(dept1N) as dept1_3months_total, 
sum(dept2N) as dept2_3months_total,
calculated dept1_3months_total+ calculated dept2_3months_total as dept1_dept2total,
(calculated dept1_3months_total/calculated dept1_dept2total)*100 as mothavg
from 
(select 
min(month) as min_date format =date9. from test) a cross join
(select * from test)b
group by ceil((intck('month', min_date, month)+1)/3);

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
  • 1662 views
  • 0 likes
  • 4 in conversation