Proc tabulate calculate column percentages with colpctsum

[eramirez]

Hi there,

I have a basic proc tabulate program but for the life of me I can't figure out how to properly calculate column percentages within a specific class variable.  I searched possible solutions, but nothing really worked.  I've done it before, but what is different now is that my dataset is structured long (vs wide) so I have several grouping variables and a numeric variable.   feel free to direct me to a posted solution. 

here is the basic code:

proc tabulate data=icu_demo missing style = [foreground=black font=(roboto) fontsize=2 just=c bordercolor=white cellwidth=43];
class characteristic_category characteristic_group season;
var count;
table characteristic_category*characteristic_group, season*(count count*colpctsum) ;

run;

I uploaded a screen shot of the output table.  What I'm looking for is the column percent for each characteristic group but using the total for each characteristic category as the denominator.  does that make sense? usually I have my data structured wide so it's a little more straight-forward. I would like to avoid having to transpose the dataset into a wide format.  I'm thinking the solution is probably staring at me in the face.   

appreciate it!

Enrique

[ballardw]

With possible issues of security from viruses and/or organization policies many on this forum will not open attachments.

You might try setting

ODS LISTING;

run the proc tabulate code, copy the result from the output window, open a text box on the forum using the </> icon above the message window and then paste the text. The text box would be important because the main message windows on this forum reformat pasted text and cells of the table would almost certainly not align properly.

For basic calculation questions you should remove all the custom formatting as it just gets in the way of the table logic.

With any of the pctsum you might at least indicate in the description which particular variable constitutes the group that perhaps should total to 100%. "Specific class variable" does not tell us which variable in your code you want.

I have a sneaking suspicion that perhaps your approach of characteristic_category*characteristic_group crosses actual values and is complicating your PCTSUM calculation.

You should also include example data in the form of a data step so we can test possible solutions.Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Or if you can't provide your data use one of the SAS supplied data sets like SASHELP.CARS or CLASS to demonstrate a similar approach.

[Patrick · Accepted solution]

@eramirez Many people prefer to propose tested SAS code and in order to do so we require you to provide some sample data shared via a SAS datastep with input/datalines that creates the data.

I believe (not sure) what you want to do is not possible with Proc Tabulate within a single table. It should be possible with Proc Report. The following sample should give you a head start: Sample 49390: Calculate percentages within groups using PROC REPORT

@ballardw The document preview option often allows to look into the content of such attachments without downloading them. Here what it shows for the current attachment. 

[ballardw]

---

@ballardw The document preview option often allows to look into the content of such attachments without downloading them. Here what it shows for the current attachment. 

---

The "document preview" opens the attachments in an application on my computer. So if that application is susceptible to any malware in the file it is a danger.

With the picture shown, the data is needed. Since the OP has not indicated "which specific subgroup" or what have you or which percentages are wanted it may or may not be possible with tabulate. Add the variable "characteristic_category" with a single variable nested is likely why tabulate currently doesn't work as intended but I am not sure what the OP actually wants as that is not shown. I suspect that going back a step and not combining all of the "characteristic_group" into a single variable but instead have one variable for each characteristic and use a nesting of characteristic_category*( 4 individual variables) likely would work.

[eramirez]

Thanks everyone for the reminders on posting, I don't do it often but good to be aware of the conventions.   

Also, thanks for the tips, I think Proc Report might work better for me.  

ER.