Output Proc Tabulate dataset exactly as it appears in SAS Report

[ebowen]

Hello all! Is it possible to output the results of PROC TABULATE to a SAS dataset exactly how it appears on the SAS Report? When I use the out= statement, SAS outputs the data with the "across" variables (year in this case) in long format, giving me two observations for each class variable, which is not what I want. I have also tried using ODS Output statement, which outputs them the same way. If I output them to excel or csv, the output looks fine, but it's very cumbersome to have to reimport the data from those sources.

Here is my code:

ods output table=test1;
proc tabulate data=lq3;
class region clustercode clustername year;
var employment_R employment_US 
	Employ_tot_R Employ_tot_US 
	income_R income_US
	Establish_R Establish_US;
table region='',clustercode=''*clustername='',
	((employment_R employment_US Employ_tot_R Employ_tot_US 
	income_R income_US Establish_R Establish_US)*year=''*sum='' (employment_R employment_US)*pctsum='');
run;
ods output close;

[Reeza]

No, you need to use PROC TRANSPOSE after on the OUT data set to get the data formatted and its not always possible. However, you can pipe the output directly to Excel, Word or PDF if that's an alternate solution?

[ebowen]

Yeah, I've tried using TRANSPOSE, but it's also pretty cumbersome. From what I understand, with multiple variables I have to transpose each one separately and then merge them all back together. Or do a data step with arrays. Anyway, I'll pursue one of these other options.

[ballardw]

You have to look at the _type_ variable as a minimum, and possibly the _page_ to tell which combination of elements are represented by any given record.

Without knowing exactly what you want to do with the data set it is hard to make further suggestions.

I do use proc tabulate occasionally to create data sets but for most uses there needs to be significant data manipulation.

Depending on what you need you might consider Proc Summary to create the data set but you would still need to look at _type_ variables to get the desired combinations.

[ed_sas_member · Accepted solution]

Hi @ebowen 

You could try to use PROC REPORT with the OUT= option instead of PROC TABULATE to have an output consistent with the report.

Best,

[ebowen]

Thank you. I haven't worked in PROC REPORT as much, so I'm not as familiar with it. But I think this may be the best way to go.

[ed_sas_member]

Hi @ebowen 

It is quite difficult without the data but maybe you could try to start with something like this to adapt the code:

proc report data=lq3 out=want (drop=_break_);
	
	by region;
	
	columns  clustercode clustername employment_R, year employment_US, year 
		Employ_tot_R, year Employ_tot_US, year income_R, year income_US, year Establish_R, year 
		Establish_US, year employment_R, pctsum employment_US, pctsum;
	
	define clustercode/group '';
	define clustername/group '';
	define year / across '';

run;

proc print data=want;
run;

- The page dimension of your PROC TABULATE (= by region) can be handled with a BY statement

- The row dimension can be handle with the GROUP option in define statements (cluster code, cluster name)

- The year can be define as an ACROSS variable to obtain as many column as years for each variable (stack under them).

- For analysis variables (numeric ones), the default statistic is SUM. For the last two ones, you can request PCTSUM by adding this statistic after a comma.

Best,