SAS XLSX Engine and PowerPivot Questions

[Residentx]

1. Does anyone know if the SAS XLSX engine can read into the powerpivot files inside of a .xlsx spreadsheet?

PowerPivot is an extension in Excel 2010 or later that allows you to go beyond the traditional structure of excel and also apply higher level calculations.

I've posted a reference to the feature detail here:

https://support.microsoft.com/en-us/office/power-pivot-overview-and-learning-f9001958-7901-4caa-ad80...

https://support.microsoft.com/en-us/office/calculations-in-power-pivot-7e4475e2-85c2-4496-9045-43141...

2. Also, where are the tech docs for details about the .XLSX engine?

3. What version is the validvarname at now? Is the latest still validvarnamev7?

4. Where do you submit feature requests for XLSX Engine?

Thanks in Advance.

[SASKiwi]

AFAIK, the SAS XLSX engine reads from and writes to Excel sheets. There is no specific functionality for pivot tables.

This link describes the capabilities in more detail.

V7 is still the current value for the VALIDVARNAME system option.

[Residentx]

Thanks for your comments. The documentation link is not being maintained.

It says it supports .xlsx but still references .xls limitations in all coding examples under .xlsx.

I can't deal with this now. I'll work on this later in the week. I'll probably need to build a sample file to get the right support for this issues.

Do I still have to buy a license to use the .xlsx engine or it a part of regular SAS and Viya now?

Thanks again.

[SASKiwi]

@Residentx  - The XLSX engine is part of the SAS/ACCESS Interface to PC Files product.

[r_behata]

As far as I am aware PowerPivot is just an extension to the Excel. It mimics some standard features in some traditional BI offerings such as  connections to external data sources,  data modelling, custom calculated measures , charts etc.

Could you be more specific about the "powerpivot files" . Are you trying to import the data model that you have created or the output you have generated using the custom measures ? 

[jimbarbour]

I am not familiar with PowerPivot, but regarding "documentation," papers written by Vince DelGobbo or Chevell Parker are typically good and go into more advanced topics.  Chevell did a demo at the SANDS meeting in 2018 where he was feeding data from SAS directly into pivot tables.    I'm not sure if it was exactly this paper:  https://support.sas.com/resources/papers/proceedings17/SAS0710-2017.pdf, but it was something along those lines.

For the VALIDVARNAME option, V7 is still commonly used, but there is something newer:  VALIDVARNAME = ANY.  SAS V7 column names are limited 32 characters and can only contain underscores, numbers, or alphabetic characters and cannot start with a number.  If however if you need to deal with longer column names or names with embedded blanks (as in databases and Excel), then you can use a name literal.  For example, if a column were named "Name that is not valid V7", you could reference it by using a name literal:  'Name that is not valid V7'n.  It looks like a hex literal, '09'x, or a date literal, '13Jun2021'd.  Name literals can be a little tricky to use, but if you really need them, they are there.  Otherwise VALIDVARNAME = V7 is your best bet.

Feature requests can be submitted via https://communities.sas.com/t5/SASware-Ballot-Ideas/idb-p/sas_ideas/label-name/sas_access.  Click on the "Suggest an idea" button.

Jim