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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.