Row percent within a column?

Reply
New Contributor
Posts: 4

Row percent within a column?

In the PROC TABULATE below, I want the percent column for each grade category (A-C, D-F and W) to be a percentage of that row within the cohort column. For example in the 2010 cohort, 51 females (70.8% of females in 2010) received a grade of A-C; 16 females (22.2% of all females in 2010) received a grade of D-F; and 5 females (6.9% of all females in 2010) received a grade of W. Instead, I am getting a percentage of the total females in all of the cohorts combined (the entire row) How can I get a percentage of that row within each year column? I am including a screenshot of the calculation as it is and the code that I used to produce it.

Cohorts.JPG

 

PROC TABULATE DATA=WORK.DEV_APPEND format=6. MISSING STYLE=[background=white] ORDER=FORMATTED;
WHERE (MaRAI_fall = '1' AND MaRGI_fall NOT IN(-1,.));
    CLASS COHORT gender raceethn has_pell_fall / STYLE=[background=LIGHTGREY];
    CLASS MaRGI_fall  / STYLE=[background=white];
    TABLES (gender={LABEL='Gender'} raceethn={LABEL='Race/Ethnic Origin'} has_pell_fall={LABEL='Has Pell'} all),
		COHORT*MaRGI_fall={LABEL='Grade Rem Math - 1 Level'}*(n='N'*f=4. rowpctN='row %'*f=4.1)*[s=[background=white CELLWIDTH=.5in]];
FORMAT gender $GENDER. raceethn $RACEETHN. MaRGI_fall DEV_GRADE.;
RUN;

 

Super User
Posts: 5,498

Re: Row percent within a column?

Posted in reply to jmartin-moreno

Back in the prehistoric days of PROC TABULATE, when there was only PCTN and not ROWPCTN to work with, you had an additional tool that is probably still available.  When specifying a percentage, you could specify which CLASS variables include and which to exclude when forming the denominator.  For example, either of these could be part of the PCTN definition:

 

<COHORT>

<GENDER>

 

I imagine that such a tool still exists.  If not, you might be forced to switch back to PCTN instead of ROWPCTN.  But you'll have to dig out the manual and experiment a little to find the denominator definition that works for what you are trying to achieve.

Super User
Posts: 11,343

Re: Row percent within a column?

Posted in reply to jmartin-moreno

If you provided some example data we might provide better results. https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... has some instructions on turning a SAS data set (or subset) into data step code that you can post here or attach as a text file to duplicate some of your data for testing.

 

Along with @Astounding's hint save the code you are testing BEFORE testing. Tabulate denominator settings are one of the places it is very easy to generate an unrecoverable error. And yes, start with PCTN.

Ask a Question
Discussion stats
  • 2 replies
  • 253 views
  • 0 likes
  • 3 in conversation