Proc tabulate out export table as in the result

[Sir_Highbury]

Dear experts,

running the following proc tabulate I get the attached output.

proc tabulate data=Check01;
class data_source YYYY;
var tot_amount;
table data_source, tot_amount*YYYY;
;run;

how can I specify: 

proc tabulate data=Check01 out=....;

in order to get the same table as in the attachment?

Thanks, SH

[Ksharp]

Could you make a format ?

proc format;
value fmt
1900=' ';
run;

proc tabulate data=Check01;
class data_source YYYY;
format YYYY fmt.;
var tot_amount;
table data_source, tot_amount*YYYY;
run;

[Reeza]

The structure of the OUT table will not match what you see. 

You could consider using ODS to output your results instead, or you need to create table via proc transpose yourself.  

[Sir_Highbury]

Dear @Reeza, is there then another way to export the table into excel?

[Reeza]

Have you tried ODS tagsets or ODS Excel?

[Sir_Highbury]

Never heard before, I am googling it despite that my initial wish was to have the table with the structure I want directly in SAS.

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets...

Anyway you are saying that there is no direct way (using out or similar) and to do it I have to write 4 additional lines of code or?

In case yes, then I reshape the data as I want using the loyal proc sql introducing two new variables.

It a pity that a so easy task can get so complicated.

[Cynthia_sas]

Hi:
You can send the PROC TABULATE table DIRECTLY to Excel using either ODS EXCEL or ODS TAGSETS.EXCELXP or, even, ODS HTML.

The key is to get the structure you want with TABULATE and then use ODS to create output that Excel can open.

Since you did not post data, it is hard to run a test program, but I'm sure I have something with SASHELP.CLASS that I can post.

cynthia

[Sir_Highbury]

Dear @Cynthia_sas and @Reeza,

thanks a lot for your suggestions, I am checking this ods excel functionality but to summarize your answer:

- I cannot do what I want using the out=.... because I got a table with structure different from the proc tabulate;

- I can use the ods excel but in this case the data I want will be directly exported (the feature looks anyway brilliant, I am exploring it);

- If I wanna get the dable that I want in sas, I should reshape it with the dear old proc sql.

[Cynthia_sas]

Hi:

  That is correct. The PROC TABULATE OUT= functionality will give you a summarized table, but the table will NOT have the same structure as the TABULATE output. However, you CAN use the Output Delivery System to create output that can be opened in a variety of destinations. Please see this sample code, that actually makes some sample data using SASHELP.PRDSALE (used because CLASS didn't have a year, but PRDSALE has a year value in it):

** make some fake data with years;
data check01(keep=data_source YYYY tot_amount);
  set sashelp.prdsale;
  if region = 'EAST' then data_source='curr month';
  else data_source='prev month';
  YYYY = year;
  if country = 'CANADA' then do;
     if region = 'EAST' then YYYY = .;
	 else if region = 'WEST' then YYYY = 1993;
  end;
  tot_amount=round(actual/1000,1);
run;
    
proc format;
  value yyfmt .=' '
        1993 = '1993'
        1994='1994';
run;
     
** need at least 9.4 M2 to create ODS EXCEL output file;
options missing = ' ';
ods html file='c:\temp\simple_tab_out.html';
ods tagsets.excelxp file='c:\temp\simple_tab_out_xp.xml' style=htmlblue;
ods csv file='c:\temp\simple_tab_out_cs.csv';
ods rtf file='c:\temp\simple_tab_out.rtf';
ods pdf file='c:\temp\simple_tab_out.pdf';
*ods excel file='c:\temp\simple_tab_out_xl.xlsx' style=htmlblue;
proc tabulate data=Check01;
  class data_source;
  class YYYY/missing;
  var tot_amount;
  table data_source, 
        tot_amount=' '*YYYY='Year'*{S={tagattr="Format:#####.00"}};
  keylabel sum=' ';
  format yyyy yyfmt.;
run;
ods _all_ close;
options missing=.;

I am NOT going to clip all the output here in the screen shot. The RTF output and PDF output look almost the same and the TAGSETS.EXCELXP and ODS EXCEL output look almost the same too. As you can see, all of the output has the same structure as the TABULATE report table -- so you don't need to use an "export" you just need to use ODS.

cynthia

Here's the output:

[thomasn528]

I have the same issue/need; it now seems to me an idea is to...

1. use PROC TABULATE to generate the exact VAR1 * VAR2 table I want, and then
2. output that table to a dedicated Excel or CSV file, and then
3. PROC IMPORT that Excel or CSV file to a SAS file.

This way if I do it once for, say, all numerators (OUT=num) and a second time for suitable denominators (OUT=denom), I can merge the two resulting files by VAR1 and have Ns, denominators, and rates per 100K all in the same record the way I need.

[ballardw]

---

@thomasn528 wrote:

I have the same issue/need; it now seems to me an idea is to...

1. use PROC TABULATE to generate the exact VAR1 * VAR2 table I want, and then
2. output that table to a dedicated Excel or CSV file, and then
3. PROC IMPORT that Excel or CSV file to a SAS file.

This way if I do it once for, say, all numerators (OUT=num) and a second time for suitable denominators (OUT=denom), I can merge the two resulting files by VAR1 and have Ns, denominators, and rates per 100K all in the same record the way I need.

---

I doubt you will be able to get Proc Import to import anything usable for most reports. VERY simple with exactly one row of header information might import. But the row headers are going to be a problem if you have more than one variable in the rows as they won't go to different variables. Most tabulate tables will tend to have column labels with multiple rows such as variable name or label and then the values of class variables before you get to the statistics. Which means the second row of information is usually treated as text so the body of the column is text.

If you get to any sort of moderately complex tabulate report with nested rows and columns you will spend way more time trying to "fix" the result of import than reshaping a Tabulate generate out= data set.

Export this to Excel or CSV (ODS Excel or ODS CSVALL) and then Import the result.

proc tabulate data= sashelp.class;
   class sex age;
   var height weight;

   table (sex all='All sexes')*(age all='All ages'),
         (height weight) *( min max mean)
         ;
run;

And this is not particularly complicated.

[thomasn528]

I see what you mean, for nested row headers -- which is what my VAR1*VAR2 implied in "PROCTABspeak".  I should have written VAR1, VAR2.
Here's the output:

And here's the messy dataset when the resulting ODS Excel file is imported:  data are out of register with their nested row headers.

I was hasty in writing VAR1*VAR2, I should have written *VAR1, VAR2*n  (...VAR2*colpctn VAR2*rowpctn) ", ie, not nested rows, but a simple single level of row, single level of column crosstab (with corresponding column and row percents if I like):

%if &colrow=CRb %then /*NNNN CCCC RRRR*/
TABLE &var1=' ' ALL='TOTAL',
(&var2=' ' ALL='Total')*n='Number'*f=comma10. (&var2=' ' ALL='Total')*colpctn='Percent'*f=&Cpctformat (&var2=' ' ALL='Total')*rowpctn='Row percent'*f=&Rpctformat)

;

I think this kind of PROC TAB output might export/import more cleanly, but I haven't tried it yet.