BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hazewu36
Calcite | Level 5

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;


pivot_test.png
1 ACCEPTED SOLUTION

Accepted Solutions
Chevell_sas
SAS Employee

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

11 REPLIES 11
hazewu36
Calcite | Level 5

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;

Chevell_sas
SAS Employee

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

hazewu36
Calcite | Level 5

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!

Chevell_sas
SAS Employee

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";

hazewu36
Calcite | Level 5

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!

Chevell_sas
SAS Employee

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.

hazewu36
Calcite | Level 5

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.

hazewu36
Calcite | Level 5

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";

Chevell_sas
SAS Employee


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"

hazewu36
Calcite | Level 5

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

hazewu36
Calcite | Level 5

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.

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
  • 11 replies
  • 4479 views
  • 3 likes
  • 2 in conversation