Create coloumns only for a subset of a dataset when using proc tabulate

[mgrasmussen]

Dear SAS experts

I have written some code which can be written in a general way, as I have done below:

proc tabulate data=datasetname;

class catvar1 catvar2 catvar3;

table catvar1*(N colpctn) catvar2*(N colpctn),

catvar3 ALL;

run;

I want to create a table containing simple frequencies and percentages. Using the code above they are presented for every level of catvar3 (one coloumn for each category). I would like to 1) summarize catvar3 for only a fifth of the observations in my dataset (for a certain geographical region). In addition to presenting the frequencies for every category of catvar3 (only for the fifth of the observations in the dataset), I would also like to summarize the following in the same table (two additional coloumns):

- 2) A summary of the frequencies in total for all categories of catvar3 for the fifth of the observations in my dataset mentioned above

- 3) A summary of the frequences in total for the entire dataset

When I use the ALL code I get 3). Can anyone give me a hint on how to get 1) and 2) as well?

Thank you

[Cynthia_sas]

Hi:
Without data, no one can run your code because they either have to find a dataset or make a dataset to test any modifications.

What you want however when you say you only want to summarize only some rows for just some region I find confusing. Is the region one of your CATVAR variables? Or is it a new variable? Do you have any sample data you can share that shows what you're getting now versus what you want to get, then it might be less confusing.
Cynthia

[Reeza]

You won't be able to get this into one table without some wrangling. So you need to either wrangle your data ahead of time into a structure that supports your analysis or wrangle your output.
I would suggest wrangling your output to get your final data set rather than messing with the input data. This means you pipe your data from PROC TABULATE using the OUT option plus PROC REPORT to display it after the fact.

[mgrasmussen]

Thank you for your replies, Cynthia and Reeze.

Reeza, this is what I feared. I could easily create a table using proc tabulate which includes the information 1) and 2) by simply restricting the dataset to the fifth of the dataset in question and then running the exact same code. However, then I am still missing 3). 

When I use output with proc tabulate the output dataset does not look like the table which is posted in the results window. If I would create a dataset that includes 1) and 2) then I could merge it with a seperate dataset only containing 3). I would simply create a running number in both datasets and merge the datasets based on this number.

However, the tricky thing for me is to create the two datasets to merge. I might be able to create the two datasets using proc tabulate and then export the table using ODS excel, whereafter I would import them back into SAS and merge them. Does it make sense?

But this seems to me to be a tedious way of doing it. I am surprised that proc tabulate - which I find is quite flexible - cannot support this operation directly.

Note I am not quite sure what you mean by "wrangling" and "piping".

Thank you

[Reeza]

---

@mgrasmussen wrote:

When I use output with proc tabulate the output dataset does not look like the table which is posted in the results window. If I would create a dataset that includes 1) and 2) then I could merge it with a seperate dataset only containing 3). I would simply create a running number in both datasets and merge the datasets based on this number.

However, the tricky thing for me is to create the two datasets to merge. I might be able to create the two datasets using proc tabulate and then export the table using ODS excel, whereafter I would import them back into SAS and merge them. Does it make sense?

But this seems to me to be a tedious way of doing it. I am surprised that proc tabulate - which I find is quite flexible - cannot support this operation directly.

---

Use the OUT= option on PROC TABULATE to have your proc tabulate table outputted to a SAS data set rather than shown in your output window. 

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n1hpbwr9acrbmen1e2d6hxjkotm1.htm#p08ap61...

Do this for all three tables. 

Then merge/append them as necessary. 

Then use PROC REPORT to display the data in the form you'd like to have seen. 

---

However, the tricky thing for me is to create the two datasets to merge. I might be able to create the two datasets using proc tabulate and then export the table using ODS excel, whereafter I would import them back into SAS and merge them. Does it make sense?

But this seems to me to be a tedious way of doing it.

---

That's a manual method. The method above is better - you can easily make a mistake when doing this in Excel. Or you're also stuck doing this every month then you need to do it every month. Or what happens when you have to do it for a new report? Doing it all in SAS is more efficient. 

---

I am surprised that proc tabulate - which I find is quite flexible - cannot support this operation directly.

---

It can, but you would need to ensure your data is structured appropriately. The method suggested above is basically an automation of your manual steps so easier to follow for a beginner. To do this you would need to re-add your data into a new variable for only those values of interest and then report them with a new period. So you would need to essentially create some fake categories or something to get what you want. I can't provide specifics because you've provided no specifics. In general though, it can but it's more work. AFAIK there's no tool that would handle this situation well, including SPSS, R, Python, PowerBI or Tableau and I've worked with all of them. 

[mgrasmussen]

Dear Reeza

Thank you for your reply.

I tried to follow your instructions but I found that I did not know what to do with the datasets produced from output using proc tabulate. I cannot really get my head around the layout of these datasets. Fx I did not know whether to append or merge the datasets and if merge I did not know which variable to merge by. Also, I did not really know how to use proc reports on these datasets.

Instead, I created the tables using proc tabulate, exported these to excel, imported the excel docs into SAS ("again") and then I was able to nicely merge the datasets. But it does not seem like the most efficient way of doing it, even though it worked 99% as I intended.

Thank you 

Martin