Below is a link to a sorted version of the same sample data file I posted earlier. This one is sorted by county->startyear->exit->agecat. Also is indicated in shading what a denominator is for all these calculations: the combination of county/start year. Based on using this denominator, I am trying to calculate the following statistics using the freq table you found in the other posting: http://www.nycourts.gov/surveys/cwcip/agerange-posting2-den.zip 1. For each county, start year, exit and agecat, the number of kids who have are in a given exitMonthCateogory (this is already done based on existing freq calculation). 2. For each county, start year, exit and agecat, the percent of kids who have are in a given exitMonthCateogory. The denominator for this is the total number who are in a particular startyear/county combination. The current percent calculation in the freq. calculation is not using the correct denominator. 3. For each county, start year, exit and agecat,  the cumulative number of kids who have are in a given exitMonthCateogory. This is just adding the numbers in each exitMonthCateogory across. 4. For each county, start year, exit and agecat,  the cumulative percent of kids who have are in a given exitMonthCateogory. The denominator for this is the total number who are in a particular startyear/county combination. 5. The total number of kids in each startyear/county combination. I was thinking that each start/county needs to be a separate table in the freq calculation, but that would create a lot of tables since there are 21 counties and 6 startyears: 126 tables. If there are any thoughts on how to determine the correct denominator for these stratafications, please let me know. Paul ... View more

After reading about 'sparse' it doesn't seem like that would lend itself to a sort. ... View more

OMG, that really worked! Thank a lot Art. Now its the percents issue and trying to calculate the correct percent and cumulative percent based on the vars in the data set. This is the issue in other thread. The percents that proc freq is calculating seem to be half of what they should be. I am answering your post in the 'proc freq: percents' thread. Paul ... View more

Hi Art I think you omitted a few variables above and when you add those back in, the columns are still ordered incorrectly (i.e. 6-48, 66, 18, 54, 1, 2, 3......). I tried sorting the data prior to running the proc freq and using the ORDER=DATA option in proc freq, but that did not work either. Paul proc freq data=s1ageshort noprint;   tables cnty_name*startyear*exit*agecat4*exitMonthCategory/out=s1ageshorttest; run; proc transpose data=s1ageshorttest out=s1ageshorttest2 (drop=_label_             rename=(_name_=DataElement));   by cnty_name startyear exit agecat4;   id exitMonthCategory; run; options validvarname=any; ... View more

When I add both outpct and outcum I am only getting the three percents and not the cumulative percents. Also, these percents on not based on the correct strata/disaggregation. I have 5 variables in the proc freq output with one being the one that is being counted. Is there a way to calculate all the percents based on how the data is stratified by the 4 variables? Paul ... View more

Yes, the transposed variable is exitMonthCategory . However, when I ran this code, I am getting the below errors. 27        by cnty_name agecat4   exit startyear notsorted; 28        id exitMonthCategory; 29        var exitMonthCategory /*?*/; 30        run; ERROR: The ID value "'60'n" occurs twice in the same BY group. ERROR: The ID value "'60'n" occurs twice in the same BY group. NOTE: The above message was for the following BY group:   cnty_name=Dutchess agecat4=3 exit=XCA startyear=2006 ERROR: The ID value "'18'n" occurs twice in the same BY group. NOTE: The above message was for the following BY group:   cnty_name=Dutchess agecat4=4 exit=XRF startyear=2009 ERROR: The ID value "'24'n" occurs twice in the same BY group. ERROR: The ID value "'24'n" occurs twice in the same BY group. NOTE: The above message was for the following BY group:   cnty_name=Orange agecat4=4 exit=ZTC startyear=2010 ERROR: The ID value "'1'n" occurs twice in the same BY group. NOTE: The above message was for the following BY group:   cnty_name=Orange agecat4=4 exit=XOT startyear=2009 ERROR: The ID value "'66'n" occurs twice in the same BY group. NOTE: The above message was for the following BY group:   cnty_name=Orange agecat4=4 exit=XRM startyear=2006 ERROR: The ID value "'36'n" occurs twice in the same BY group. NOTE: The above message was for the following BY group:   cnty_name=Orange agecat4=3 exit=XRF startyear=2007 ERROR: The ID value "'18'n" occurs twice in the same BY group. NOTE: The above message was for the following BY group:   cnty_name=Orange agecat4=4 exit=XRF startyear=2006 ERROR: The ID value "'18'n" occurs twice in the same BY group. NOTE: The above message was for the following BY group:   cnty_name=Orange agecat4=3 exit=XRF startyear=2006 ERROR: The ID value "'12'n" occurs twice in the same BY group. NOTE: The above message was for the following BY group:   cnty_name=Orange agecat4=3 exit=XRF startyear=2008 ERROR: The ID value "'24'n" occurs twice in the same BY group. NOTE: The above message was for the following BY group:   cnty_name=Dutchess agecat4=4 exit=ZTC startyear=2010 ERROR: The ID value "'2'n" occurs twice in the same BY group. ERROR: Too many bad BY groups. NOTE: View WORK.S1AGE1V.VIEW used (Total process time):   real time       0.04 seconds   cpu time        0.03 seconds   ... View more

I don't know how to attach files to the posting, but below is a link to a zip file containing data: http://www.nycourts.gov/surveys/cwcip/agerange-posting2.zip ... View more

The problem with prefix is that is just creates columns 1->17, printing: 'month1', 'month'2, 'month3', etc. My columns are actually: 1, 2, 3, 4, 5, 6, 12, 18, 24, 30, 36.........66. They need to be printed as is. The above will also print sequential columns 1-20. Is there a way to print what is actually there? Paul ... View more

I don't completely understand the above code, but when I run it, I am getting the below error on the transpose statement: ERROR: Variable cnty_name is not on file WORK.S1AGE2. When I look at both S1AGE2 and s1age1v, cnty_name is there? Paul ... View more

1. what does 'low' refer to in proc format? 2. How are you automatically classifying the values in time in program as belonging to one of the month categories? Paul ... View more

My data ranges (at top of the spreadsheet) are just individual months or 6 month groupings. How do they correspond to the below values (i.e. -0.084931)? proc format;   value timespan     low-0.084931=1     0.0849310001-0.167123=2     0.1671230001-0.252054=3     other=4; run; ... View more

Hi Arthur Two questions among many, but two to start: 1. How are you coming up with the month ranges along the top of the spreadsheet? From the code, I am not seeing how this is determined. 2. The first proc format does what? Paul ... View more