BookmarkSubscribeRSS Feed
TashaBee
Fluorite | Level 6

I decided to stop while I am ahead. My goal is to create a workbook that has a few mulitple pivot tables and also format the numbers. When I attempt the process for formating, the data part is blank. I do not get the mulitple worksheets. I am new to this approach so I am sure that I missed something.

 

Here is my snippet for the code:

 

filename temp url

'http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl';

%include temp;

 

 

ods tagsets.tableeditor file="&path\enroll.html"

options(

pivot_series="yes"

button_text = "PivotTable"

AUTO_EXCEL="Yes"

pivotpage = "county_nm,race_desc | county_nm,race_desc | county_nm,race_desc"

pivotrow = "PROGRAM_AREA_DESC | CURRICULUM_NM | DEGREE_PURSUED"

pivotcol = "reporting_term,gender_code | reporting_term,gender_code | reporting_term,gender_code"

pivotdata = "student_cnt | studednt_cnt | student_cnt"

pivotdata_stats = "sum | sum | sum"

/*pivotdata_fmt="#,###"*/

excel_save_file="&path\enroll.xls"

/*SHEET_NAME="Program Area"*/);

proc print data=enroll noobs;

run;

ods tagsets.tableeditor close;

5 REPLIES 5
ballardw
Super User

I don't use the tableeditor tagset so can't make a targeted response. We would need some example data, please provide as a data step we can run as importing from an Excel file is likely not to duplicate your data in terms of type and other characteristics.

 

One thing that you should look at if the example code was copy and paste: you have a variable studednt_cnt that I suspect you meant to be student_cnt.

I have no idea what a mis-spelled word does in the tagset but I doubt it is going to do what you intend.

TashaBee
Fluorite | Level 6
That spelling error was typed in quickly because I had initially removed it. Is there another way to pivot tables with tableeditor? I took a class and that was used.
ballardw
Super User

I don't use pivot tables unless someone threatens me so I'm not very helpful with that.

 

Actually I haven't actually done an Excel pivot table in something like seven years. With SAS I determine with my report customers what information they want and generate a table that looks that way using one of the SAS report procedures generally Print or Tabulate and occasional Report.

Then output can go to ODS RTF, PDF or tagsets.Excelxp (or all at the same time if needed).

 

LinusH
Tourmaline | Level 20
I haven't used tastes either, and haven't heard of the possibility to do pivoting directly from it. Not saying it couldn't be done...
For pivots I think that either SAS Addin for MS Office or use SAS data as a data source (OLEDB or ODBC) are more widely used.
Data never sleeps
Oligolas
Barite | Level 11

Hi,

 

I didn't manage yet to get the tableeditor work correctly for me, fulfilling all the requirements I had. So I chose to work with ODS Tagset ExcelXp to output my data and added some vbs

 

This paper helped me to program my pivot table directly from SAS, works like a charm

http://www2.sas.com/proceedings/sugi31/035-31.pdf

 

After inserting the pivot I convert my xml to a native xlsx file
one way to do this can be found here:
http://support.sas.com/kb/43/496.html

 

Finally i noticed that I had to refresh the xlsx. Although it can be done with 3 clics in excel, I chose to do this directly from SAS

 

The main vbs code you need to do this (using the proceedings described in the above paper) is:

 

         put " Dim oXL ";
         put " Dim objWorkBook";
         put " Set oXL = CreateObject(""Excel.Application"")";

         %*Open xlsx file, refresh (pivot) Table and save;
         put " Set objWorkBook = oXL.Workbooks.Open(""&xlsxFileName."")";

         put " objWorkBook.RefreshAll";
         put " oXL.ActiveWorkBook.SaveAs ""&xlsxFileName."",51";
         put " oXL.DisplayAlerts = True";
         put " oXL.ActiveWorkBook.Close SaveChanges = True";
         put " oXL.Quit";
________________________

- Cheers -

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
  • 5 replies
  • 1049 views
  • 0 likes
  • 4 in conversation