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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.