DATA Step, Macro, Functions and more

Proc tabulate out export table as in the result

Reply
Frequent Contributor
Posts: 127

Proc tabulate out export table as in the result

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


Capture.PNG
Super User
Posts: 9,681

Re: Proc tabulate out export table as in the result

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;





Super User
Posts: 17,836

Re: Proc tabulate out export table as in the result

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.  

Frequent Contributor
Posts: 127

Re: Proc tabulate out export table as in the result

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

Super User
Posts: 17,836

Re: Proc tabulate out export table as in the result

Have you tried ODS tagsets or ODS Excel?

Frequent Contributor
Posts: 127

Re: Proc tabulate out export table as in the result

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.

 

SAS Super FREQ
Posts: 8,743

Re: Proc tabulate out export table as in the result

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
Frequent Contributor
Posts: 127

Re: Proc tabulate out export table as in the result

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.

SAS Super FREQ
Posts: 8,743

Re: Proc tabulate out export table as in the result

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:

using_ODS_for_TABULATE.png

 

Ask a Question
Discussion stats
  • 8 replies
  • 1440 views
  • 2 likes
  • 4 in conversation