Hi All
I am trying to calculate AVERAGE MONTHLY PRICE using the PROC SQL Code but stryggling to get the right coding.
The data looks like this :
Dates Amount
01Jul1994 1000
02MAY1995 1500
10MAY1995 2000
09JUN1995 3000
27JUN1995 3500
.
.
.
.
.
.AND SO ON FOR DIFFENRENT MONTHS IN DIFFERENT YEARS.
How to go abou it? Any suggestion will be helpfull
Thanks a ton in advance.
data have;
input Dates : date9. Amount;
format dates date.;
cards;
01Jul1994 1000
02MAY1995 1500
10MAY1995 2000
09JUN1995 3000
27JUN1995 3500
;
run;
proc summary data=have nway;
class dates;
format dates monyy7.;
var amount;
output out=want mean=;
run;
Create a variable called MONTH, you can use the MONTH function in SAS to do this. Also create a variable called YEAR, using the YEAR function in SAS.
Then run PROC MEANS or PROC SUMMARY with YEAR and MONTH as CLASS variables.
Something like:
proc sql; create table WANT as select MNTH, avg(AMOUNT) as AVG from (select month(DATES) as MNTH,AMOUNT from HAVE) group by MNTH; quit;
Something like
proc sql;
create table want as
select put(dates,yymmn6.) as yymm, mean(amount) as mean_price
from have
group by calculated yymm
;
quit;
??
data have;
input Dates : date9. Amount;
format dates date.;
cards;
01Jul1994 1000
02MAY1995 1500
10MAY1995 2000
09JUN1995 3000
27JUN1995 3500
;
run;
proc summary data=have nway;
class dates;
format dates monyy7.;
var amount;
output out=want mean=;
run;
@Ksharp wrote:
data have; input Dates : date9. Amount; format dates date.; cards; 01Jul1994 1000 02MAY1995 1500 10MAY1995 2000 09JUN1995 3000 27JUN1995 3500 ; run; proc summary data=have nway; class dates; format dates monyy7.; var amount; output out=want mean=; run;
Certainly a superior method than what I described above.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.