Solved: Re: how to calculate average

Jahanzaib · Posted 11-29-2016 04:33 AM

I want to create variable avglev in which i want values which make the aveage accroding to Industries(FFI12). There are 9 countries and 12 Industries(FFI12). So i want to make the average(Mean) value of bdr according to countries and industries. like for one country there would be average value of bdr for 12 different indsutries(FFI12)

data work.new;
infile datalines dsd truncover;
input Global_Company_Key:32. SICC:32. CoName:$29. company:$21. fam:32. Country:$7. year:32. ACCUMULATED_DEPRECIATION:32. BVPS:32. CASH:32. Sales:32. csout:32. cs:32. Bequity:32. Mequity:32. mdr:32. CGS:32. CAT:32. MTB:32. CLT:32. DEPRECIATION:32. DEPRECIATION_AND_DEPLETION:32. DEPRECIATION_DEPLETION_AMORT:32. EBIT:32. EPS:32. EBIT___DEPRECIATION:32. FIXED_ASSETS___COMMON_EQUITY:32. LTD:32. MP:32. MARKET_PRICE_YEAR_END:32. MV:32. ND:32. NT:32. OTHER_PROCEEDS_FROM_SALE_ISSUA:32. PROPERTY__PLANT___EQUIP_GROSS:32. assmat:32. FA:32. Tdebt:32. bdr:32. RD:32. STD:32. TA:32. TOTAL_DEBT:32. TOTAL_DEBT___COMMON_EQUITY:32. TOTAL_DEBT___TOTAL_ASSETS:32. TOTAL_DEBT___TOTAL_CAPITAL_STD:32. EBIT_TA:32. RD_TA:32. FA_TA:32. lnTA:32. EBITDA:32. FFI12:32.;
datalines;
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2015,26321,1.376,831105,9.49,1499360,2225723,2063119.36,2383982.4,0.2500901691,90.18,2260330,1.0752692705,1315517,15961,15961,37673,95865,0,133538,10.61,795043,1.59,1.77,3755.11,-1067326,1246706,0,245150,1.75186E-22,218829,795043,0.1865042064,0,0,4262869,795043,38.55,18.65,27.58,0.0224883758,0,0.0513337379,15.265452966,58192,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2009,24128,0.919,633776,-24.97,677460,677460,622585.74,1077161.4,0,99.5,801447,1.4887169365,233660,3345,3345,5756,-150857,0.21,-145101,1.7,0,1.59,2.23,704.56,-633776,225376,0,34687,7.753538E-21,10559,0,0,0,0,930141,0,0,0,0,-0.162187238,0,0.0113520423,13.743091467,-156613,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2014,12903,1.365,1092244,33.87,1496060,2219647,2042121.9,2378735.4,0.2496908534,41.86,2203858,1.1054257309,291595,13659,13659,29668,13200,0,42868,11.19,791605,1.59,2.51,1461.98,-900862,108516,0,241399,6.25196E-22,228496,791605,0.2479268827,0,0,3192897,791605,38.77,24.79,27.87,0.0041341766,0,0.0715638494,14.976439213,-16468,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2011,29417,1.062,718208,-13.43,677460,677460,719462.52,1077161.4,0,82.72,936676,1.3434567586,242887,2974,2974,5794,62011,0,67805,1.35,0,1.59,1.27,948.44,-718208,185184,0,39135,6.244823E-21,9718,0,0,0,0,1041467,0,0,0,0,0.059541973,0,0.0093310686,13.856140854,56217,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2013,30689,1.04,221743,-10.45,854960,854960,889158.4,1359386.4,0.1830111742,79.17,1293125,1.3283999452,223410,2490,2490,3322,-23514,0.12,-20192,1.1,304512,1.59,1.71,1077.16,-872678,193067,200010,40436,2.498569E-21,9747,304512,0.2126664599,0,0,1431876,304512,34.24,21.27,25.34,-0.016421813,0,0.0068071537,14.174496031,-26836,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2010,25363,0.914,619709,-6.24,677460,677460,619198.44,1077161.4,0,80.93,788938,1.4957822192,230889,2815,2815,5471,-13261,0,-7790,1.52,0,1.59,1.4,1510.74,-619709,211639,0,34786,9.864866E-21,9423,0,0,0,0,923718,0,0,0,0,-0.014356113,0,0.0102011653,13.736162109,-18732,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2000,43076,0.283,6472,-0.58,85759,85759,24269.797,136356.81,0.5658115189,51.59,53802,1.429910069,137413,6278,6278,6278,8443,0,14721,492.74,103983,1.59,1.47,90.05,171221,167561,0,162472,3.934351E-18,119396,177693,0.6815420256,0,73710,260722,177693,733.33,68.15,90.19,0.0323831514,0,0.4579437102,12.471209985,2165,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2007,19579,1.72,728263,-1.29,681481,681481,1172147.32,1083554.79,0,88.61,979986,0.942421572,317969,7195,7195,9885,358102,0,367987,0.7,0,1.59,3.74,840.61,-728263,358412,248644,27728,2.949982E-21,8149,0,0,6977,0,1538641,0,0,0,0,0.232739151,0.004534521,0.0052962322,14.246410117,348217,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2008,22828,1.133,617471,-16.67,677460,677460,767562.18,1077161.4,0,98.48,843015,1.2903341007,186028,2487,2487,5217,-414174,0.27,-408957,1.47,0,1.59,1.04,2548.74,-621448,293223,5700,34118,5.898552E-21,11290,0,0,0,0,1066355,0,0,0,0,-0.388401611,0,0.0105874685,13.879756849,-419391,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2005,96607,0.883,150445,-0.22,493981,493981,436185.223,785429.79,0.1251001421,52.86,470654,1.4088078743,334453,14418,14418,16729,-14298,0,2431,16.66,4020,1.59,0.68,474.22,-38138,531298,0,169235,3.445525E-20,72628,112307,0.1314608451,6007,108287,854300,112307,25.76,13.15,17.86,-0.016736509,0.0070314878,0.0850146319,13.658037699,-31027,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2012,31481,1.033,310152,-14.23,677460,677460,699816.18,1077161.4,0,88.42,825907,1.3908174802,195867,2799,2799,3596,-19723,0,-16127,1.79,0,1.59,1.59,860.37,-652829,208387,0,44013,7.89011E-21,12532,0,0,0,0,965528,0,0,0,0,-0.020427165,0,0.0129794268,13.780430381,-23319,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2006,94320,0.955,302383,7.63,591981,591981,565341.855,941249.79,0.0972660664,55.49,670777,1.3667984944,353856,14036,14036,17155,43131,0.05,60286,12.18,6423,1.59,1.42,335.91,-200967,584832,103961,163178,1.514373E-20,68858,101416,0.0989583689,7921,94993,1024835,101416,17.94,9.9,13.23,0.0420857992,0.0077290491,0.0671893524,13.840042182,25976,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2002,57750,0.889,114117,11.78,446259,446259,396724.251,709551.81,0.1779067572,47.56,422283,1.4453295889,209645,12575,12575,23361,23774,0,47135,33.35,60264,1.59,0.9,423.94,29061,387377,0,190121,6.119737E-20,132371,153552,0.2185908724,0,93288,702463,153552,38.69,21.86,26.38,0.0338437754,0,0.1884383946,13.46234801,413,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2001,48176,0.858,123499,-0.06,446259,446259,382890.222,709551.81,0.1185361225,53.43,246185,1.5606365062,170191,7432,7432,9789,9903,0,19692,32.9,10014,1.59,0.95,126.07,-33166,231425,211840,174189,1.528078E-19,126013,95418,0.1637621812,0,85404,582662,95418,24.91,16.38,19.4,0.0169961315,0,0.2162711829,13.275362537,114,6
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2004,90301,0.972,102506,10.96,493981,493981,480149.532,785429.79,0.2362535744,50.08,505572,1.3218649956,393071,17338,17338,26737,41092,0.05,67829,17.35,4324,1.59,0.96,523.62,116264,531848,0,173586,2.768891E-20,83285,242961,0.2561601648,6421,238637,948473,242961,50.62,25.62,30.6,0.0433243751,0.006769829,0.0878095634,13.762608602,14355,6
202779,4922,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2003,73622,0.936,90281,15.18,493981,493981,462366.216,785429.79,0.1972490849,50.01,445760,1.3822445931,271289,16916,16916,31398,42681,0.05,74079,19.75,43233,1.59,1.06,401.63,82201,481345,0,164880,3.961094E-20,91258,192993,0.2283467921,3749,149760,845175,192993,41.76,22.83,26.74,0.0504996007,0.0044357677,0.1079752714,13.647298985,11283,8
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2015,19019058,47.213,9785230,34.31,10005799,31274178,472403788.19,501190471.91,0.1160562781,141.82,51791052,1.1528315082,44080330,2650653,2650653,2963823,7739893,4.01,10703716,64.85,52077258,50.09,52.38,78088.5,45226171,73652902,0,58134480,2.863356E-27,39115422,65803172,0.3493559077,84612,13725914,188355687,65803172,251.57,76.26,94.09,0.0410918997,0.0004492139,0.2076678577,19.053842685,4776070,8
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2009,7536614,28.147,6758884,40.37,5346059,461956,150475522.67,267784095.31,0.0504517267,137.89,18072857,2.7791962072,13136114,1320254,1320254,1478334,3284460,1.97,4762794,60.84,9893427,50.09,58.393,35929.21,3732846,25938443,0,26814126,6.751525E-26,19277512,14227997,0.2157932513,8268,4334570,65933466,14227997,157.38,62.17,73.58,0.0498147633,0.0001253991,0.292378259,18.004156701,1806126,8
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2014,17460674,46.331,10620831,35.63,9991775,31272626,462928927.53,500488009.75,0.1081723049,135.09,48324082,1.2146885592,46536490,2366988,2366988,2631650,6796813,3.07,9428463,68.77,38984960,50.09,66.09,97677.75,43044157,56861737,0,58024199,3.738584E-27,40563525,60705607,0.3469946157,89390,21720647,174946827,60705607,213.91,75.72,90.52,0.0388507361,0.0005109553,0.2318620217,18.97999264,4165163,8
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2011,10994668,34.231,7951348,25.12,8046741,804674,275447991.17,403061256.69,0.0670037892,136.05,34890404,2.2575017606,22957443,1678516,1678516,1853394,4366785,2.23,6220179,69.97,22171572,50.09,48.72,55389.92,14289927,33126213,3385362,37545128,1.452369E-26,26550460,28946132,0.2852353304,19729,6774560,101481580,28946132,162.06,61.74,78.25,0.0430303214,0.0001944097,0.2616283664,18.435387862,2513391,8
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2013,15308033,44.106,7845894,25.19,9706175,970617,428100554.55,486182305.75,0.084829737,133.55,41419097,1.379322723,37554984,2274129,2274129,2449970,5867664,2.59,8317634,72.19,33788083,50.09,81.77,57447.3,28636969,48766983,2296645,54683441,5.753142E-27,39375408,45065622,0.2943164436,88742,11277539,153119620,45065622,192.81,67.4,83.47,0.0383207848,0.00057956,0.2571545567,18.846730004,3417694,8
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2010,9108306,30.834,14690280,31.56,6656890,570413,205258546.26,333443620.1,0.0585322264,153.55,30600296,2.5328704997,23407315,1391655,1391655,1524918,3726223,2.14,5251141,64.91,13109184,50.09,50.608,63977.1,3603539,33960838,0,31398426,2.131171E-26,22290120,20730606,0.247901986,21481,7621422,83624203,20730606,254.78,67.99,85.7,0.0445591452,0.0002568754,0.2665510606,18.241843546,2201305,8
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2000,1365006,10.899,3747073,-71.47,677610,126623,7385271.39,33941484.9,0.1140074517,149.21,6449211,2.7316690623,4073407,358954,358954,431057,804088,0.77,1235145,101.97,1910306,50.09,15.861,7625.62,275595,5226217,26065,7940149,2.946569E-24,6575143,4367511,0.284795258,2932,2457205,15335617,4367511,67.82,28.54,32.15,0.0524327127,0.0001911889,0.4287498182,16.545688589,373031,8
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2007,5194425,23.797,8233646,6.63,1239601,122222,29498784.997,62091614.09,0.087092279,161.93,13246599,1.731577143,9550583,893840,893839,1020556,1806563,0.41,2827119,63.79,3282325,50.09,38.142,10345.95,-2349727,11319755,3630639,22255453,1.696034E-25,17061028,5923600,0.1329608532,2834,2641275,44551459,5923600,22.15,13.31,15.86,0.04055003,0.0000636118,0.3829510499,17.612155461,786007,8
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2008,6227372,26.582,5765933,62.81,4043845,354207,107493487.79,202556196.05,0.0457531245,122.46,14355582,2.697517564,10739348,1208927,1209337,1353228,2768013,1.34,4121241,60.71,6321742,50.09,32.667,42197.71,2136009,19929369,95832,24229002,1.331587E-25,18001630,9711930,0.173424175,4295,3390188,56001016,9711930,166.09,59.24,72.09,0.0494279068,0.000076695,0.3214518465,17.840880391,1414785,8
;;;;

Kurt_Bremser · Posted 11-29-2016 05:17 AM

Then merge the results of means back into the dataset:

proc sort data=new;
by country ffi12;
run;

proc means data=new mean nway noprint;
by country ffi12;
var bdr;
output
  out=means (keep=country ffi12 avglev)
  mean(bdr)=avglev
;
run;

data want;
merge
  new (in=a)
  means
;
by country ffi12;
run;

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

View solution in original post

Kurt_Bremser · Posted 11-29-2016 04:58 AM

proc sort data=new (keep=country ffi12 bdr) out=have;
by country;
run;

proc means data=have mean nway;
class ffi12;
by country;
var bdr;
output
  out=want (drop=_type_)
  mean(bdr)=avglev
;
run;

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Jahanzaib · Posted 11-29-2016 05:07 AM

I don't need output in table format. I want to calculate average of each industry(for 12 industries) for each country and place that in a new column avglev.

Kurt_Bremser · Posted 11-29-2016 05:17 AM

Then merge the results of means back into the dataset:

proc sort data=new;
by country ffi12;
run;

proc means data=new mean nway noprint;
by country ffi12;
var bdr;
output
  out=means (keep=country ffi12 avglev)
  mean(bdr)=avglev
;
run;

data want;
merge
  new (in=a)
  means
;
by country ffi12;
run;

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

rogerjdeangelis · Posted 11-29-2016 09:56 AM

SAS forum: Adding average MPG city by country and cartype to each observation


HAVE

Up to 40 obs WORK.CARSRT total obs=35

Obs    ORIGIN    TYPE    MPG_CITY

  1    Asia      Hybrid     46
  2    Asia      SUV        17
  3    Asia      SUV        20
  4    Asia      Sedan      18
  5    Asia      Sedan      24
  6    Asia      Sedan      18
  7    Asia      Sports     20
  8    Asia      Sports     19
  9    Asia      Sports     17
 10    Asia      Truck      15
 11    Asia      Truck      24
 12    Asia      Wagon      15
 13    Asia      Wagon      26
 14    Asia      Wagon      16
 15    Europe    SUV        16
 16    Europe    Sedan      17
 17    Europe    Sedan      22
 18    Europe    Sedan      20
 19    Europe    Sports     20
 20    Europe    Sports     15
 21    Europe    Sports     16
 22    Europe    Wagon      18
 23    Europe    Wagon      19
 24    Europe    Wagon      19
 25    USA       SUV15      15
 26    USA       SUV19      19
 27    USA       Sedan      14
 28    USA       Sedan      20
 29    USA       Sedan      18
 30    USA       Sports     17
 31    USA       Sports     17
 32    USA       Truck      13
 33    USA       Truck      15
 34    USA       Wagon      22
 35    USA       Wagon      17


WANT


Up to 40 obs from CarSrtAvg total obs=35

Obs    ORIGIN    TYPE      MPG_CITY     MPGAVG

  1    Asia      Hybrid       46       46.0000

  2    Asia      SUV          17       18.5000
  3    Asia      SUV          20       18.5000

  4    Asia      Sedan        18       20.0000
  5    Asia      Sedan        24       20.0000
  6    Asia      Sedan        18       20.0000

  7    Asia      Sports       20       18.6667
  8    Asia      Sports       19       18.6667
  9    Asia      Sports       17       18.6667

 10    Asia      Truck        15       19.5000
 11    Asia      Truck        24       19.5000

 12    Asia      Wagon        15       19.0000
 13    Asia      Wagon        26       19.0000
 14    Asia      Wagon        16       19.0000

 15    Europe    SUV          16       16.0000

 16    Europe    Sedan        17       19.6667
 17    Europe    Sedan        22       19.6667
 18    Europe    Sedan        20       19.6667

 19    Europe    Sports       20       17.0000
 20    Europe    Sports       15       17.0000
 21    Europe    Sports       16       17.0000

 22    Europe    Wagon        18       18.6667
 23    Europe    Wagon        19       18.6667
 24    Europe    Wagon        19       18.6667

 25    USA       SUV          15       17.0000
 26    USA       SUV          19       17.0000

 27    USA       Sedan        14       17.3333
 28    USA       Sedan        20       17.3333
 29    USA       Sedan        18       17.3333

 30    USA       Sports       17       17.0000
 31    USA       Sports       17       17.0000

 32    USA       Truck        13       14.0000
 33    USA       Truck        15       14.0000

 34    USA       Wagon        22       19.5000
 35    USA       Wagon        17       19.5000


SOLUTION

* create some data;
proc sort data=sashelp.cars(keep=origin type drivetrain mpg_city) out=carsrt(drop=drivetrain) nodupkey;
by origin type drivetrain;
run;quit;

* use the DOW loop;
data CarSrtAvg(keep=origin type mpg_city mpg_city MpgAvg);

  retain origin type;
  retain mpg_city  MpgAvg MpgCnt .;

  do until (last.type);

     set carsrt;
     by origin type;

     MpgSum=sum(MpgSum,mpg_city);
     MpgCnt=sum(MpgCnt,1);

  end;

  MpgAvg=MpgSum/MpgCnt;

  do until (last.type);

     set carsrt;
     by origin type;
     output;

  end;

  MpgSum=0;
  MpgCnt=0;

run;quit;

rogerjdeangelis · Posted 11-29-2016 10:02 AM

SAS forum: Adding average MPG city by country and cartype to each observation


HAVE

Up to 40 obs WORK.CARSRT total obs=35

Obs    ORIGIN    TYPE    MPG_CITY

  1    Asia      Hybrid     46
  2    Asia      SUV        17
  3    Asia      SUV        20
  4    Asia      Sedan      18
  5    Asia      Sedan      24
  6    Asia      Sedan      18
  7    Asia      Sports     20
  8    Asia      Sports     19
  9    Asia      Sports     17
 10    Asia      Truck      15
 11    Asia      Truck      24
 12    Asia      Wagon      15
 13    Asia      Wagon      26
 14    Asia      Wagon      16
 15    Europe    SUV        16
 16    Europe    Sedan      17
 17    Europe    Sedan      22
 18    Europe    Sedan      20
 19    Europe    Sports     20
 20    Europe    Sports     15
 21    Europe    Sports     16
 22    Europe    Wagon      18
 23    Europe    Wagon      19
 24    Europe    Wagon      19
 25    USA       SUV15      15
 26    USA       SUV19      19
 27    USA       Sedan      14
 28    USA       Sedan      20
 29    USA       Sedan      18
 30    USA       Sports     17
 31    USA       Sports     17
 32    USA       Truck      13
 33    USA       Truck      15
 34    USA       Wagon      22
 35    USA       Wagon      17


WANT


Up to 40 obs from CarSrtAvg total obs=35

Obs    ORIGIN    TYPE      MPG_CITY     MPGAVG

  1    Asia      Hybrid       46       46.0000

  2    Asia      SUV          17       18.5000
  3    Asia      SUV          20       18.5000

  4    Asia      Sedan        18       20.0000
  5    Asia      Sedan        24       20.0000
  6    Asia      Sedan        18       20.0000

  7    Asia      Sports       20       18.6667
  8    Asia      Sports       19       18.6667
  9    Asia      Sports       17       18.6667

 10    Asia      Truck        15       19.5000
 11    Asia      Truck        24       19.5000

 12    Asia      Wagon        15       19.0000
 13    Asia      Wagon        26       19.0000
 14    Asia      Wagon        16       19.0000

 15    Europe    SUV          16       16.0000

 16    Europe    Sedan        17       19.6667
 17    Europe    Sedan        22       19.6667
 18    Europe    Sedan        20       19.6667

 19    Europe    Sports       20       17.0000
 20    Europe    Sports       15       17.0000
 21    Europe    Sports       16       17.0000

 22    Europe    Wagon        18       18.6667
 23    Europe    Wagon        19       18.6667
 24    Europe    Wagon        19       18.6667

 25    USA       SUV          15       17.0000
 26    USA       SUV          19       17.0000

 27    USA       Sedan        14       17.3333
 28    USA       Sedan        20       17.3333
 29    USA       Sedan        18       17.3333

 30    USA       Sports       17       17.0000
 31    USA       Sports       17       17.0000

 32    USA       Truck        13       14.0000
 33    USA       Truck        15       14.0000

 34    USA       Wagon        22       19.5000
 35    USA       Wagon        17       19.5000


SOLUTION

* create some data;
proc sort data=sashelp.cars(keep=origin type drivetrain mpg_city) out=carsrt(drop=drivetrain) nodupkey;
by origin type drivetrain;
run;quit;

* use the DOW loop;
data CarSrtAvg(keep=origin type mpg_city mpg_city MpgAvg);

  retain origin type;
  retain mpg_city  MpgAvg MpgCnt .;

  do until (last.type);

     set carsrt;
     by origin type;

     MpgSum=sum(MpgSum,mpg_city);
     MpgCnt=sum(MpgCnt,1);

  end;

  MpgAvg=MpgSum/MpgCnt;

  do until (last.type);

     set carsrt;
     by origin type;
     output;

  end;

  MpgSum=0;
  MpgCnt=0;

run;quit;

Jahanzaib · Posted 11-29-2016 11:52 PM

in create some data;
i didn't see carsrt (drop=drivertrain). i didnt see drivertrain in your example data. in dow loop you mentioned mpg_city two times; what are mpgcount and mpgsum?

The 2025 SAS Hackathon has begun!

SAS Training: Just a Click Away