I am looking for number, cumulative number, percent, cumulative percent and total. I am attaching a link to an example of what the ultimate output from SAS would look like in Excel. So I am I have a data table that has many child records per county and I need to summarize these records by county, and then by things like age range, year--all the dimensions that are in the link to the attached sample file. I don't know how to summarize a data table though. I can do some things in the Enterprise Guide GUI with Procs, but I don't get everything and it is not as neatly organized as the linked example. Any ideas on approach, etc. would be appreciated. http://www.nycourts.gov/surveys/cwcip/metric1-Example.zip Paul ... View more

For this, I don't have much of a data step to do it actually. I am not even sure it can be done. The existing data table (s1) has the following columns (among many others, but the below are key): entity_id (unique id) age_category (1-4 indicator of the age range) county (county) ExitMonths (the time in months it takes to reach the court milestone) ExitMonthCategory (either a 6, 12, 18, 24 indicator) startYear (year child entered the court system) What I would like to do is to export a new data set (ultimately to Excel) creating columns for month ranges (i.e. 0-6, 7-12, 13-18, etc.) and rows for summarized measures and dimensions (i.e. county, age range, number, percent, cumulative percent). The row dimensions would summarize the above ExitMonths column from the s1 data table. I tried using Enterprise Guide to run a frequency over the s1 table, but could not get what I want. Any ideas? Paul ... View more

I think that did it, thank you. Paul ... View more

Hi Art I believe you have identified the issue, but I don't completely understand all your code yet. The issue is that I have a population of kids in court that I need to find a median for. The total number of kids is known ahead of time and I want to capture a median when 50% of the kids have reached a certain court event--I can't capture the median based on just the number of kids who have reached the event prior 50% reaching it. I need a median for the entire population. So, when 50% have achieved the event, I can run the median. I am new to SAS so I don't understand all your code above, but from what I understand, the second marco will run only when result set is ge 50%? Paul ... View more

Proc rank will not necessarily work because the percent option only calculates percents for non-missing values: "Percent: divides each rank by the number of observations that have nonmissing values of the variable and multiplies the result by 100 to get a percentage...." While this makes sense, I need to factor in missing values in determining a median. Paul ... View more

The situation I have is getting a median for a group of kids who experience a specific court event. We know what and who the overall population of the kids is--that number is fixed. What we want to do is to present the median value for the overall population for the time it takes 50% of these kids to reach this specific court event when 50% of the kids have reached it. We know that we obviously don't know how long it will take the other 50% of these kids to reach the event, but that is for another statistic--maybe the 3rd percentile. Regardless, I am looking for a way to take a median for a population where the median incorporates missing values in its calculation--if 50% of observations have occurred, then the median can be presented. If 50% have not occurred, then the median cannot be calculated. I understand the lifetest works for this, but I am having trouble with this and was wondering if the median calc. can be configured to accommodate missing values? Paul ... View more

Yes, that is it. Thank you both. One follow up, I see that for generating the medians in Summary Statistics, the Proc means procedure is used. I believe this does not include missing values in determining the median? Only populated values? If so, is there a way to use proc means to factor in missing values into median calculations? Paul ... View more

Yes, can you please point me to the lifetest resource? Also, on the function to export results of this into a file, the below code is part of a much larger set of code. Is there parameter that just points to a specific PROC or DATA section of code and not the whole thing? Paul ods tagsets.excelxp file='Temp.xml'; proc means data=s10sorted median q1 q3 stddev; var dur_filing_issuejoin dur_issuejoin_dispo; run; ods tagsets.excelxp close; ... View more

Thanks Reeza. 1. I was experimenting with proc means, so stdev was just part of that. 2. No, I want the null values to be factored in. What I want to do is to be sure the median and quartile calculations are done when 50% or 75% of the observations have actually occurred. I don't want to calculate the either based on SAS just calculating it for the values that are present. So if I have 10 potential observations and only 4 of them have occurred as of a certain date, I should not be able to calculate a median for that variable. The way proc means seems to work is that it will calculate a median for the 4 present values and exclude the 6 that are missing. Now if there were 5 observations (out of 10), I should be able to calculate a median because half of the observations have occurred and it doesn't matter what the remaining values are, they are already past the median point. So I am wondering if this is something that must be coded or whether there is a configuration or another procedure that accomplishes this? 3. Thank you. I will try this. Paul ... View more

That seemed to have worked. I found the nodupkey, but adding the second sort just using the docket_id made it work completely. Thanks to everyone. Paul ... View more

That was the second option, thanks. I am sorting both data sets prior to merging. Both sorts are by docket_id. Can I add a second sort by a date column (edate) and then just keep the first instance of edate (where it is lowest)? In the below code, do you know how would I only keep the first (lowest) value of edate? proc sort data=work.issuejoinedevents; by docket_id edate; run; ... View more