Conditionally formatting data using proc tabulate

[haydn89]

Hello all,

I was hoping someone could help me to better format my proc tabulate output to look like the final output that I am trying to achieve.

Two questions come to mind:

1.    

sI      * Is it possible to conditionally assign variables to certain class levels?

2.      * How can I use proc format or proc tabulate to conditionally format my cell values?

To emphasise what I mean, take a look at the output that I am trying to achieve (table1_1.xls) compared to the output that is automatically generated (table1_1.xml) from the sas code (table1_1.sas). The data set used to create the tables is also attached (all.sas7bdat).

For each of the three years there is a column representing the estimated average for the population, and a relative standard error (table1_1.xml). Our workplace style guide dictates that the only year that relative standard errors (rse) values are output is for the ‘year of interest,’ which is 2011-12 in this instance.

Is it possible to manipulate my code so that only rse values are output for 2011-12 (i.e. like table1_1.xls) rather than each year (i.e. table1_1.xml)?

At the moment, I am using excel to format the results after I have exported the data in an xml format using tagsets.excelxp. All of my financial results have to formatted as [ # ### ##0;-# ### ##0 ] (table1_1.xls). Financial results (i.e. table rows with units labelled ‘$’) in 2010-11 need to be rounded to the nearest 10, 2011-12 financial results need to be rounded to the nearest 100 and 2012-13 results need to be rounded to the nearest 1000. My results for “rate of return” and “interest to receipts ratio” have to be rounded to the first decimal place, while all other percentage variables are rounded to the nearest whole number. Finally, my rse values also need rounded to the nearest whole number and have brackets.

Is there a way to apply these formats using proc tabulate or proc format? Formatting the results using SAS rather than manually in excel would save me a lot of time.

Any help is appreciated

[ballardw]

sI      * Is it possible to conditionally assign variables to certain class levels?
2.      * How can I use proc format or proc tabulate to conditionally format my cell values?

I haven't found any way to conditionally assign things the way you're thinking of. What you are looking for requires some sort of pre-processing the data.

The good news is that you can probably use the output from your existing tabulate output, add and OUT=moredata to the Proc Tabulate statement.

You will have a bunch more variables, one for each column. So you use that dataset as input to another proc tabulate and exclude the RSE variables for the not wanted years. Your table statement will get a bit longer as you'll have more variables.

[haydn89]

Thanks ballardw. I didn't think about approaching the problem that way.

[Cynthia_sas · Accepted solution]

Hi:

Take a look at page 13/14 of this paper and Figure 15 https://support.sas.com/resources/papers/proceedings13/366-2013.pdf the example shows changing the format of AGE, based on the value of the NAME and AGE...the change is ONLY to the column where the name is equal to ALICE and AGE is equal to 13. So that is a complex condition because 2 values are checked. I could have written it to only check for the value of NAME (which would be similar to your CLASS variable) example.

PROC TABULATE does not have this capability to change the format of one cell based on the value of another cell. But, PROC REPORT can change the value of one cell based on the value in another cell.

cynthia

[haydn89]

Thanks heaps Cynthia!