Trying to load .sas7bdat file - data are incomplete but no error or warning received

I tried loading a .sas7bdat file using 'Input Data' tool.

The workflow was successful, as on below screen - I can see 5864 records were imported, no errors or warnings in the log:

SAS load - Alteryx.png

However, when I check the table properties in SAS it actually has 341,435 records:

SAS load - SAS.png

Did anyone else ran into similar issues with importing SAS datasets? Why does Alteryx drop a huge portion of the table with no warning?

Note - I'm on Alteryx Designer 11.0 x64

Michal

Input

Accepted answers

MichalK

OK, I was able to get pretty close to the source of this.

A bit of background on how the table is created: Data are pulled from a web application each month and saved as tab-delimited text file. Next, a SAS program is ran to append all monthly loads to create a historized table.

What seems to be throwing Alteryx off are special characters in the oldest files - most commonly 'Zurich' in a column containing a city name.

In monthly cuts that import to Alteryx with no issues, it appears with an umlaut character: "Zürich".

In the ones that cause issues, it appears as "ZErich". I noticed that similarly, in names and surnames that contain special characters the character is replaced by an 'E'.

Examining the files in Notepad++, the values initially look the same - 'ZErich.

After changing encoding to UTF-8, it looks like this:

zurich UTF-8.png

After changing encoding back to ANSI, it looks like this:

zurich ANSI.png

I don't know much about encoding, but maybe someone who does can get some insight from this.

Curiously, the case is not that Alteryx does not load rows including these values. Some are loaded, although with an underscore added: 'Z_Erich'.

I wasn't able to track down exactly what setup causes Alteryx to stop the data load.

Lastly, I tried importing the source data (txt files) directly into Alteryx - that worked fine, and Zurich is loaded without an underscore as 'ZErich'.

So at some stage of SAS working with the data these values are saved in a form that causes problems for Alteryx. I looked at SAS programs and there is no explicit formatting logic coded for these values.

I hope you found this interesting

All comments

mbarone

Have you experienced this with other sas files, or just this one? I input sas files occasionally and have not experienced this.

MichalK

Hi,

I was able to replicate this behavior with 3 other files - the underlying data is loaded from the same application, so it is likely something in the way it's structured that throws Alteryx off.

I'm now very curious and will keep digging

Wayne_Chen

Hi MichalK,

I have the same problem as you.

I tried reading a .sas7bdat file using 'Input Data' tool.

The workflow could run successfully and there was no error or warning message, but after checking the results, it was found that there were 140,000 original data, and only more than 3,000 were read in.

Therefore, I would like to ask if you have found a solution to this problem.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703