Getting a range of values using the pcfiles engine

Accepted Solution Solved
Reply
Super Contributor
Posts: 266
Accepted Solution

Getting a range of values using the pcfiles engine

I only want to read cells A1 through G111 but my sas data set has A1 through HH111.  I'm wondering if this is because the excel file was created by my copy/paste from another excel file.

 

libname t pcfiles path="G:\Departments\Research\TESTING\SC alt\&year.\Data Quality Checks\Post-Day 45\ADST download.xlsx";
proc contents data=t._all_;
run;
*GET ADST DOWNLOAD*;
data adst;
set t.'Sheet1$'n;
run;


Accepted Solutions
Solution
‎11-16-2016 10:55 AM
Super User
Super User
Posts: 7,392

Re: Getting a range of values using the pcfiles engine

This is a question that comes up quite often, and it is because of the way Excel works (another arrow in its quiver of why not to use in the first place).  Behind the scenes Excel holds a lot of data about what is on the various sheets, not just what the value is.  For instance, in Excel if you look at the underlying structure for the Cell object, it has formatting properties, borders, colors, and other such information.  A cell is not considered to be empty if the .Value property is missing, but something else is present.  Excel looks at the various properties of the cells and sets its Datarange property for the sheet, based on not just the .Value, but other properties.  Therefore when SAS queries this to get the datarange, it may indicate cells which don't show any values.  You should be able to check this by selecting the columns to the right of your data and deleting them - this effectively creates new empty cells, and the datarange will reflect your data.

 

Personally I would save your data to a proper data transfer file format, CSV or XML, and then write a datastep and infile to read it in correctly.

View solution in original post


All Replies
Solution
‎11-16-2016 10:55 AM
Super User
Super User
Posts: 7,392

Re: Getting a range of values using the pcfiles engine

This is a question that comes up quite often, and it is because of the way Excel works (another arrow in its quiver of why not to use in the first place).  Behind the scenes Excel holds a lot of data about what is on the various sheets, not just what the value is.  For instance, in Excel if you look at the underlying structure for the Cell object, it has formatting properties, borders, colors, and other such information.  A cell is not considered to be empty if the .Value property is missing, but something else is present.  Excel looks at the various properties of the cells and sets its Datarange property for the sheet, based on not just the .Value, but other properties.  Therefore when SAS queries this to get the datarange, it may indicate cells which don't show any values.  You should be able to check this by selecting the columns to the right of your data and deleting them - this effectively creates new empty cells, and the datarange will reflect your data.

 

Personally I would save your data to a proper data transfer file format, CSV or XML, and then write a datastep and infile to read it in correctly.

Super Contributor
Posts: 266

Re: Getting a range of values using the pcfiles engine

If it's saved as XML, does the LIBNAME statement along with PCFILES still work?

Super User
Super User
Posts: 7,392

Re: Getting a range of values using the pcfiles engine

Nope, for XML you can use the XML mapper tool to define the schema.  Depends on how complicated the XML is.  Its generally simpler to use CSV.

http://www2.sas.com/proceedings/sugi29/119-29.pdf

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 197 views
  • 1 like
  • 2 in conversation