Help using Base SAS procedures

Conditionally formatting data using proc tabulate

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Conditionally formatting data using proc tabulate

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

Attachment
Attachment

Accepted Solutions
Solution
‎03-22-2014 12:44 AM
SAS Super FREQ
Posts: 8,740

Re: Conditionally formatting data using proc tabulate

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

View solution in original post


All Replies
Super User
Posts: 10,466

Re: Conditionally formatting data using proc tabulate

 

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.

Occasional Contributor
Posts: 11

Re: Conditionally formatting data using proc tabulate

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

Solution
‎03-22-2014 12:44 AM
SAS Super FREQ
Posts: 8,740

Re: Conditionally formatting data using proc tabulate

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

Occasional Contributor
Posts: 11

Re: Conditionally formatting data using proc tabulate

Thanks heaps Cynthia!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1617 views
  • 3 likes
  • 3 in conversation