topic Re: SAS XLSX Engine and PowerPivot Questions in SAS Programming

SAS XLSX Engine and PowerPivot Questions

Residentx — Sun, 13 Jun 2021 23:35:38 GMT

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-028a6d2432ed

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

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.

Re: SAS XLSX Engine and PowerPivot Questions

SASKiwi — Mon, 14 Jun 2021 00:19:38 GMT

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.

Re: SAS XLSX Engine and PowerPivot Questions

Residentx — Mon, 14 Jun 2021 01:08:00 GMT

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.

Re: SAS XLSX Engine and PowerPivot Questions

r_behata — Mon, 14 Jun 2021 01:50:53 GMT

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 ?

Re: SAS XLSX Engine and PowerPivot Questions

SASKiwi — Mon, 14 Jun 2021 01:56:33 GMT

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

Re: SAS XLSX Engine and PowerPivot Questions

jimbarbour — Mon, 14 Jun 2021 04:15:57 GMT

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