I'm using SAS Enterprise Guide 7.1...I have a dataset that I am working to aggregate variables from using two methods: 1) sum and 2) weighted by observation total. A sample of the sort of data that I'm working with is below. So I have multiple IDs that are associated with a single Code variable. Ideally, I'm trying to combine the data to look like the output dataset below (there are something like 2,000 codes with anywhere from 2 to 15 IDs associated with each). Some of the variables are combined simply by summing each observation (i.e. delay) and some are combined by summing the length % times the variable (i.e. at_rur, at_sub, and postspd).   Is there a way to use something like proc means with a by statement to produce the new dataset, but still be able to define how the variables are combined? Thank you in advance for your help!   i.e.  input dataset ID Code Length Length % at_rur at_sub postspd delay 100380 107-10114           1.29           0.28 1 0 55               -   100494 107-10114           0.55           0.12 1 0 55           0.17 100382 107-10114           0.39           0.08 1 0 55               -   100341 107-10114           0.59           0.13 1 0 55               -   100386 107-10114           0.56           0.12 0 1 55               -   100539 107-10114           0.61           0.13 0 1 55               -   103878 107-10114           0.37           0.08 0 1 50               -   100551 107-10114           0.18           0.04 0 1 50               -   103029 107-10114           0.13           0.03 0 1 50               -     Total           4.66                    0.61                  0.39               54.28           0.17   output dataset Code Length at_rur at_sub postspd delay 107-10114            4.66           0.61           0.39               54.28                  0.17 ... View more

Hopefully this is the right location...I've done a decent amount of digging, but can't seem to find any similar questions.   I'm using SAS Enterprise Guide 7.1...I'm trying to clean up a fairly large dataset that is calculating the harmonic mean and standard deviation of 24 hour variables (each beginning with Spd_). I would like to write the script in a way that reviews the 24 bins if the standard deviation is greater than 10, drops the largest value, re-calculates the standard deviation and mean, and continues to test each record until the standard deviation for each is below 10. The basic form that I've been trying to use is below, but it doesn't actually work. Is it possible to do this and if so, how? Thank you!   I've also attached an example of one record and what should happen with each iteration until the record no longer meets the review condition (the attachment is the excel file of the image posted below...I wasn't sure if the image would be expandable or not).   data work.analysis;    set work.analysis_set;    if StDev>10 then       do;       largest(1, of Spd_:)=.;       StDev=std(of Spd_:);       Mean=harmean(of Spd_;);    end;    repeat...   i.e. ... View more

I'm attempting to collapse a dataset and calculate new summary variables by two classes...The data I'm working with is made up of a year's worth of observations split into hours, i.e.    ID Date Time Value 1 1/1/2014 0:00 5 1 1/1/2014 1:00 5 ... 1 1/2/2014 0:00 10 1 1/2/2014 1:00 10 ... 2 1/1/2014 0:00 5 2 1/1/2014 1:00 5 ...   What I want to do is collapse the dataset by ID and time while adding variable counts and sums, i.e.   ID Time N Sum 1 0:00 2 15 1 1:00 2 15 ...   I've tried a few different ways, proc sql, proc means, and if-else statements within a data step, but proc means has been the closest.   proc means data=dataset sum;      var value;      classes ID time;      output out=newdataset; run;   This gives me the right values, but since there are multiple times within each ID class, there are blanks (see attached image). So the output dataset appears to re-group, re-calculate, and include additional statistics...is there any way to just get the table that is produced from the proc means statement as a new dataset and fill in the ID (tmc_code) values? If not, is there another way to collapse the dataset while still getting the values I need?    The other method I tried was proc sql, however I'm definitely unfamiliar with proc sql so I'm not sure what the issue is. The output is simply a new variable (sum_value) with the total of all values, not categorized by either ID or time.   proc sql;      create table newdataset as      select *, sum(value) as sum_value      from dataset      group by ID and time; quit;   proc print (etc)...   This is a task that I will likely be doing a lot (collapsing and calculating new variables) so I really appreciate the help. Thank you! ... View more