BookmarkSubscribeRSS Feed
Sir_Highbury
Quartz | Level 8

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
8 REPLIES 8
Ksharp
Super User
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
Super User

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
Quartz | Level 8

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

Reeza
Super User

Have you tried ODS tagsets or ODS Excel?

Sir_Highbury
Quartz | Level 8

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
SAS Super FREQ
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
Quartz | Level 8

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
SAS Super FREQ

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 16432 views
  • 2 likes
  • 4 in conversation