topic Re: Proc tabulate out export table as in the result in SAS Programming

Proc tabulate out export table as in the result

Sir_Highbury — Fri, 08 Jul 2016 08:35:25 GMT

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

Re: Proc tabulate out export table as in the result

Ksharp — Fri, 08 Jul 2016 09:08:06 GMT

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;

Re: Proc tabulate out export table as in the result

Reeza — Fri, 08 Jul 2016 12:24:29 GMT

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.

Re: Proc tabulate out export table as in the result

Sir_Highbury — Fri, 08 Jul 2016 13:06:25 GMT

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

Re: Proc tabulate out export table as in the result

Reeza — Fri, 08 Jul 2016 13:07:59 GMT

Have you tried ODS tagsets or ODS Excel?

Re: Proc tabulate out export table as in the result

Sir_Highbury — Fri, 08 Jul 2016 13:29:25 GMT

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-from-sas/

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.

Re: Proc tabulate out export table as in the result

Cynthia_sas — Fri, 08 Jul 2016 14:21:37 GMT

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

Re: Proc tabulate out export table as in the result

Sir_Highbury — Fri, 08 Jul 2016 14:35:41 GMT

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.

Re: Proc tabulate out export table as in the result

Cynthia_sas — Fri, 08 Jul 2016 15:05:11 GMT

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:

Re: Proc tabulate out export table as in the result

thomasn528 — Wed, 20 Nov 2024 23:12:24 GMT

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

use PROC TABULATE to generate the exact VAR1 * VAR2 table I want, and then
output that table to a dedicated Excel or CSV file, and then
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.

Re: Proc tabulate out export table as in the result

ballardw — Wed, 20 Nov 2024 23:26:53 GMT

@thomasn528 wrote:

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

use PROC TABULATE to generate the exact VAR1 * VAR2 table I want, and then

output that table to a dedicated Excel or CSV file, and then

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.

Re: Proc tabulate out export table as in the result

thomasn528 — Thu, 21 Nov 2024 01:13:55 GMT

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.