The SAS Output Delivery System and reporting techniques

PIVOT table using SAS - ods tagsets.tableeditor options

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

PIVOT table using SAS - ods tagsets.tableeditor options

Hello, first of all, thank you previous discussion regarding ODS tagsets.tableeditor. It helped a lot to do PIVOT automation project.  Below is my code to create a PIVOT table by using SAS.

However, i can only get three summation columns, I won't be able to get the rest. Also, same problem, the sum data shown as row rather than column, even i added the option of

pivotdata_tocolumns="yes".

Could you help me to take a look at my codes below and see anything wrong...? Super thanks!!! I attached the result i got from the code below.

Also, my actual dataset contains about 260,000 obs. when I print the whole dataset, i never be able to open the HTML file i created. Is that because my dataset too big? any suggestions to solve this problem?

ods tagsets.tableeditor file="/directory/pivot_test.html"

options(

button_text = "Create PIVOT table"

auto_excel ="yes"

excel_save_file="/directory/pivot_test.xlsx"

quit="NO"

pivotpage = "Reco,

          R_Method,

          Po,

          M,

          P_G,

         A_N,

          B_Q,

          Gr,

          S_P,

         C_O,

         P_I,

         F_E"

pivotrow = "Month,M_day"

pivotdata_tocolumns="yes"

pivotdata = "P_Count,

          P_Amount,

          P_Average,

          R_Count,

          R_Amount,

          R_Average,

          N_N_Count,

          N_N_Amount,

          N_N_Average,

          N_Re"

pivotdata_stats = "sum"

);

proc print data=app.stef (obs=4000);

var Reco

    R_Method

    Po

    M

    P_G

    A_N

    B_Q

    Gr

    S_P

    C_O

    P_I

    F_E   

    Month

     M_day

    P_Count

    P_Amount

    P_Average

    R_Count

    R_Amount

    R_Average

    N_N_Count

    N_N_Amount

    N_N_Average

   N_Re;

run;

ods tagsets.tableeditor close;

Attachment

Accepted Solutions
Solution
‎10-24-2014 01:19 PM
SAS Employee
Posts: 88

Re: PIVOT table using SAS - ods tagsets.tableeditor options

Yes, the TableEditor.tpl file is the file that I have suggested that you %include after adding the ODS PATH statement. After you have done this, you can then update an existing file which Excel can read such as an XML file with a pivot table by using the options OUTPUT_TYPE="script"  which will generate a  script file that will be executed at the end of the step.  The UPDATE_TARGET="target workbook"  is used to update the workbook with the pivot tables. You will also need to specify the sheet name of where we will get the columns using the SHEET_NAME= option.  The last step is to execute the script which is done with the X statement in the code.

View solution in original post


All Replies
Occasional Contributor
Posts: 8

Re: PIVOT table using SAS - ods tagsets.tableeditor options

I used below codes and it finally works on small sample. But I still can not generate the pivot when i tried the whole dataset, which is about 270,000 obs.

ods tagsets.tableeditor file="/directory/pivot_test.html"

options(

button_text = "Create PIVOT table"

auto_excel ="yes"

excel_save_file="/directory/pivot_test.xlsx"

quit="NO"

pivotpage = "Reco,

          R_Method,

          Po,

          M,

          P_G,

         A_N,

          B_Q,

          Gr,

          S_P,

         C_O,

         P_I,

         F_E"

pivotrow = "Month,M_day"

pivotdata_tocolumns="yes"

pivotdata = "P_Count,

          P_Amount,

          P_Average,

          R_Count,

          R_Amount,

          R_Average,

          N_N_Count,

          N_N_Amount,

          N_N_Average,

          N_Re"

);

proc print data=app.stef (obs=4000);

var Reco

    R_Method

    Po

    M

    P_G

    A_N

    B_Q

    Gr

    S_P

    C_O

    P_I

    F_E   

    Month

     M_day

    P_Count

    P_Amount

    P_Average

    R_Count

    R_Amount

    R_Average

    N_N_Count

    N_N_Amount

    N_N_Average

   N_Re;

run;

ods tagsets.tableeditor close;

SAS Employee
Posts: 88

Re: PIVOT table using SAS - ods tagsets.tableeditor options

You might want to try updating an existing file rather than try and create this intermediate HTML file do to the size. See the information from the post below with instructions on how to do this.

https://communities.sas.com/message/222633#222633

Occasional Contributor
Posts: 8

Re: PIVOT table using SAS - ods tagsets.tableeditor options

Thanks Chevell!

I tried that earlier too. But when I tried to compile the tagset file you attached, below errors occured.

ERROR: Template 'Tagsets.Tableeditor' was unable to write to template store!

ERROR: Template 'Styles.MyStyle' was unable to write to template store!

ERROR: Errors printed on pages 119,120.

Should I used the “%include statement” instead? What is the %include statement I should Put into my SAS codes?

Thanks!

SAS Employee
Posts: 88

Re: PIVOT table using SAS - ods tagsets.tableeditor options

The error occurs because there is not an item store on the default search path that has update  access. Add the below ODS PATH statement which will add the WORK.Templat item store to the beginning of the search path with update access.

ods path(prepend) work.templat(update);

%include "tableditor tagset";

Occasional Contributor
Posts: 8

Re: PIVOT table using SAS - ods tagsets.tableeditor options

do u mean i include these two statements,     ods path(prepend) work.templat(update); %include "tableditor tagset"; 

on the codes below you provided on that discussion?

/* Create file to update */

ods tagsets.Excelxp file="c:\temp.xml"  options(sheet_name="temp") ;

proc print data=sashelp.class;;

run;

ods tagsets.Excelxp close;

/* update the file with a simple pivot table  by creating a javascript file that we execute */

options xnowait noxsync;

ods noresults;

ods tagsets.tableeditor file="c:\testing_source.js"

                                 options(output_type="script"

                                             update_target="c:\\temp.xml"

                                             sheet_name = "temp"

                                             pivotrow = "age,sex"

                                             pivotdata = "height,weight"

                                             pivotdata_tocolumns = "yes" );

data _null_;

file print;

put "test";

run;

ods tagsets.tableeditor close;

/* execute Script file */

x "c:\testing_source.js";

The error occured when I submitted the tableEditor file your shared with us. By the way, there are four sas program on that file, tableEditor.tpl, enhance_maro.sas, Preselected_filters.sas, Zero_Obs_tagset.sas.

Is that equivalent if I include these two statements u mentioned, to submitting these 4 programs first?

Thanks!

Solution
‎10-24-2014 01:19 PM
SAS Employee
Posts: 88

Re: PIVOT table using SAS - ods tagsets.tableeditor options

Yes, the TableEditor.tpl file is the file that I have suggested that you %include after adding the ODS PATH statement. After you have done this, you can then update an existing file which Excel can read such as an XML file with a pivot table by using the options OUTPUT_TYPE="script"  which will generate a  script file that will be executed at the end of the step.  The UPDATE_TARGET="target workbook"  is used to update the workbook with the pivot tables. You will also need to specify the sheet name of where we will get the columns using the SHEET_NAME= option.  The last step is to execute the script which is done with the X statement in the code.

Occasional Contributor
Posts: 8

Re: PIVOT table using SAS - ods tagsets.tableeditor options

Hello Chevell,

I got below errors. when i tried this method to update existing pivot tables.

6807       options xnowait noxsync;

ERROR 13-12: Unrecognized SAS option name XNOWAIT.

6807     ! options xnowait noxsync;

ERROR 13-12: Unrecognized SAS option name NOXSYNC.

Below is my code to do the update pivot tables.

=========================================================

libname app "/directory/data";

ods path (prepend) work.templat(update);

%include "/direcotry/tableEditor.sas";

/* Create file to update */

ods tagsets.Excelxp file="/directory/file.csv"  options(sheet_name="Report2b") ;

proc print data=app.stef;

run;

ods tagsets.Excelxp close;

/* update the file with a simple pivot table  by creating a javascript file that we execute */

options xnowait noxsync;

ods noresults;

ods tagsets.tableeditor file="/directory/data/testing_source.js"

   options(output_type="script"

    update_target="/directory/file.csv"

    sheet_name = "Report"

    pivotpage = "Reco,

          R_Method,

          Po,

          M,

          P_G,

         A_N,

          B_Q,

          Gr,

          S_P,

         C_O,

         P_I,

         F_E"

pivotrow = "Month,M_day"

pivotdata_tocolumns="yes"

pivotdata = "P_Count,

          P_Amount,

          P_Average,

          R_Count,

          R_Amount,

          R_Average,

          N_N_Count,

          N_N_Amount,

          N_N_Average,

          N_Re"

);

data _null_;

file print;

put "test";

run;

ods tagsets.tableeditor close;

/* execute Script file */

x "/directory/data/testing_source.js";

=======================================================

Appreciate you could take a look at and advise the solution. Thank you so much.

Occasional Contributor
Posts: 8

Re: PIVOT table using SAS - ods tagsets.tableeditor options

Hello Chevell,

I comment out these two options since they look like options for windows. And im actually execting the SAS program on UNIX Server. Below are codes. However, it can not work out. I also attached my result excel sheet for your reference. It would be much appreciated if you can take a look at and let me know what is the problem on my codes.  Thank you so much.

To clarify, the steps of ods tagsets.Excelxp, is to write the sas dataset into the excel file, which has the updated dataset to create a simple pivot table..?

then we use the steps of ods tagsets.tableeditor, to create the pivot table?

I tried to open the output csv file, it always said file not loaded completed. Did I miss anything on my codes..? Thanks

libname app "/directory/data";

ods path (prepend) work.templat(update);

%include "/directory/source/tableEditor.sas";

/* Create file to update */

ods tagsets.Excelxp file="/directory/data/test.csv"  options(sheet_name="Report2b") ;

proc print data=app.stef;

run;

ods tagsets.Excelxp close;

/* update the file with a simple pivot table  by creating a javascript file that we execute */

/*options xnowait noxsync; */

ods noresults;

ods tagsets.tableeditor file="/directory/data/testing_source.js"

   options(output_type="script"

    update_target="/direcotry/data/test.csv"

    sheet_name = "Report2b"

    pivotpage = "Reco,

          R_M,

          P,

          M,

          P_G,

         A_N,

          B_Q,

          G,

          S_P,

         C_O,

         P_I,

         F_E "

pivotrow = "Month,M_day "

PIVOTDATA_TOCOLUMNS="yes"

pivotdata = "P_Count,

          P_Amount,

          P_Average,

          R_Count,

          R_Amount,

          R_Average,

          NCount,

          NAmount,

          NAverage,

          N_Reco"

);

data _null_;

file print;

put "test";

run;

ods tagsets.tableeditor close;

/* execute Script file */

x "/directory/data/testing_source.js";

Attachment
SAS Employee
Posts: 88

Re: PIVOT table using SAS - ods tagsets.tableeditor options


The UPDATE_TARGET= options requires that each slash in the path be escaped with another slash. So the option would be specified as below. Also, this would only work on windows. If you have further questions, please direct it to the Technical Support tracking entry.

Update_target="C:\\directory\\file"

Occasional Contributor
Posts: 8

Re: PIVOT table using SAS - ods tagsets.tableeditor options

thanks! yea, i found out one paper u wrote regarding this. I will try and see...Thank u.

Occasional Contributor
Posts: 8

Re: PIVOT table using SAS - ods tagsets.tableeditor options

any options regarding creating pivots will be available on UNIX...? I submitted my SAS program on remote UNIX server.....>< i also updated on technical support tracking entry....appreciate you guys' suggestion.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 2042 views
  • 3 likes
  • 2 in conversation