Paul, I hadn't responded until now because (1) I had my real job to do and (2) I'm not sure if I understand your requirements. However, that said, I also misunderstood what proc freq was using as the denominator for table percents, thus had to revise the code a bit. You can easily amass the desired denominator by including a datastep with a double dow loop in it. Actually, the entire code could be wrapped within the same datastep but, honestly, I thought that would be too difficult to follow. Regardless, you said that the code wasn't correctly producing want3. You had defined want3 as: 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. That is what want 3 is producing. Here are two lines from the results, the first showing want1 (the counts) and the second showing want3 (the cummulative counts across the row): cnty_name startyear exit agecat4 DataElement 1 Month 2 Months 3 Months 4 Months 5 Months 6 Months 7-12 Months 1-1.5 Years >1.51-1.99 Years 2-2.5 Years 2.51-2.99 Years 3-3.5 Years 3.51-3.99 Years 4-4.5 Years 4.51-4.99 Years 5-5.5 Years >5.5 Years County-CLPR 2006 XCA 1 want1 0 0 0 0 0 0 1 2 4 6 6 5 8 2 0 0 0 County-CLPR 2006 XCA 1 want3 0 0 0 0 0 0 1 3 7 13 19 24 32 34 34 34 34 If the above is not what you are seeking, then I don't understand the requirements. On the other hand, if it is what you wanted, then try the following revised code: options validvarname=v7; PROC IMPORT OUT= WORK.s1ageshort DATAFILE= "D:\art\agerange-posting2.xlsx" DBMS=XLSX REPLACE; GETNAMES=YES; DATAROW=2; RUN; proc sort data=s1ageshort; by cnty_name startyear; run; proc freq data=s1ageshort noprint; tables exit*agecat4*exitMonthCategory/ out=s1ageshorttest (drop=percent pct_col pct_tabl rename=( count=want1 pct_row=want2 )) outpct sparse; by cnty_name startyear; run; proc transpose data=s1ageshorttest out=want (drop=_label_ rename=(_name_=DataElement)); by cnty_name startyear exit agecat4; id exitMonthCategory; run; data want; do until (last.startyear); set want; array row(17) _1--_72; by cnty_name startyear; if first.startyear then total=0; if DataElement='want1' then total=sum(total,sum(of row(*))); end; do until (last.startyear); set want; by cnty_name startyear; output; end; run; data want (drop=tot: i); set want; by cnty_name startyear exit agecat4; array row(17) _1--_72; array tot(17); do i=1 to 17; if missing(row(i)) then row(i)=0; end; if first.startyear then do i=1 to 17; tot(i)=row(i); end; else if DataElement eq 'want1' then do i=1 to 17; tot(i)+row(i); end; output; if DataElement eq 'want1' then do; DataElement='want3'; do i=2 to 17; row(i)+row(i-1); end; output; DataElement='want4'; do i=1 to 17; row(i)=100*row(i)/total; end; output; end; if last.startyear then do; DataElement='want5'; do i=1 to 17; row(i)=tot(i); end; call missing(exit); call missing(agecat4); output; end; run; options validvarname=any; data want; set want (rename=( _1='1 Month'n _2='2 Months'n _3='3 Months'n _4='4 Months'n _5='5 Months'n _6='6 Months'n _12='7-12 Months'n _18='1-1.5 Years'n _24='>1.51-1.99 Years'n _30='2-2.5 Years'n _36='2.51-2.99 Years'n _42='3-3.5 Years'n _48='3.51-3.99 Years'n _54='4-4.5 Years'n _60='4.51-4.99 Years'n _66='5-5.5 Years'n _72='>5.5 Years'n)); run;
... View more
