BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
haydn89
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

4 REPLIES 4
ballardw
Super User

 

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
Obsidian | Level 7

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

Cynthia_sas
SAS Super FREQ

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
Obsidian | Level 7

Thanks heaps Cynthia!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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