topic Re: Cross sectional averages in SAS Procedures

Cross sectional averages

spraynardz90 — Mon, 20 Aug 2012 13:36:30 GMT

I am looking to calculate cross sectional averages.

Is this possible on sas?

I have attached a small sample of my data below.

Re: Cross sectional averages

LinusH — Mon, 20 Aug 2012 14:06:22 GMT

I'm not sure if understand you correctly, but I think you can start with proc means, or SQL (BMDECILE, CFDECILE, select avg(RET), avg(VWRETD), avg(EWRETD) from x group by BMDECILE, CFDECILE;)

Re: Cross sectional averages

spraynardz90 — Mon, 20 Aug 2012 14:16:44 GMT

Could you please be more specific?

Re: Cross sectional averages

LinusH — Mon, 20 Aug 2012 14:20:52 GMT

Sample SQL:

Proc sql;

select

BMDECILE, CFDECILE, avg(RET) as retavg, avg(VWRETD) as vwretdavg, avg(EWRETD) as ewretdavg

from x.y

group by BMDECILE, CFDECILE;

quit;

Re: Cross sectional averages

kevinmc — Mon, 20 Aug 2012 15:03:02 GMT

%macro avmacr;

%do i=1 %to 10;

data avgs&i;

set v4;

avg=mean(of RET VWRETD EWRETD);

where BMDECILE=&i and CFDECILE=&i;

%end;

run;

%mend;

%avmacr;

here you get 10 datasets and using set to combine all this 10 datasets.

Re: Cross sectional averages

SteveDenham — Mon, 20 Aug 2012 15:09:02 GMT

proc means data=yourdata nway;

class bmdecile cfdecile;

var RET VWRETD EWRETD;

run;

This will give the 300 means, standard deviations, mins and maxes as output.

If you want a dataset as output, either switch to proc summary or add the following:

proc means data=yourdata nway noprint;

class bmdecile cfdecile;

var RET VWRETD EWRETD;

output out=meandata mean=/autoname;

run;

Steve Denham

Re: Cross sectional averages

kevinmc — Mon, 20 Aug 2012 16:17:04 GMT

stevedenham................

he wants avg of RET VWRETD EWRETD when BMDECILE=1 and CFDECILE=1 or

BMDECILE=2 and CFDECILE=2 or

BMDECILE=10 and CFDECILE=10

Re: Cross sectional averages

kevinmc — Mon, 20 Aug 2012 16:18:59 GMT

this one is good compare to my previous code

data data1;

set data;

avg=mean(of RET VWRETD EWRETD);

where (BMDECILE=1 and CFDECILE=1) or (BMDECILE=2 and CFDECILE=2) or

(BMDECILE=3 and CFDECILE=3) or (BMDECILE=4 and CFDECILE=4) or

(BMDECILE=5 and CFDECILE=5) or (BMDECILE=6 and CFDECILE=6) or

(BMDECILE=7 and CFDECILE=7) or (BMDECILE=8 and CFDECILE=8) or

(BMDECILE=9 and CFDECILE=9) or (BMDECILE=10 and CFDECILE=10);

run;

Re: Cross sectional averages

MikeZdeb — Mon, 20 Aug 2012 16:28:54 GMT

hi ... given this problem statement ...

"I would approximately like to calculate 300 averages (100 for RET, 100 for VWETD, 100 for EWRETD). "

and ...

"when BMDECILE=1,2,3,4,5,6,7,8,9, 10 AND CFDECILE=1,2,3,4,5,6,7,8,9,10. (ie: 10*10=100)."

I'm voting for Steve

Re: Cross sectional averages

SteveDenham — Mon, 20 Aug 2012 16:29:24 GMT

I don't think so. See: "For example: The average of RET VWRETD EWRETD when CFDECILE=1 and BMDECILE=5. Or the average of RET VWRETD EWRETD when CFDECILE=3 and BMDECILE=7. etc." in the original post.

He wants 10x10 = 100 means for each of three variables. PROC MEANS or PROC SUMMARY is ideal for this.

Steve Denham

Re: Cross sectional averages

spraynardz90 — Mon, 20 Aug 2012 23:51:01 GMT

Quite right Steve. Thanks for the input everyone.