topic Re: Need help with importing in SAS Programming

Need help with importing

YimingWeng — Mon, 16 Dec 2019 22:07:59 GMT

Hi Gurus,

I am trying to read in a xlsx file, where one column has values like these:

After proc import, some values become the values shown below. Can someone tell me how to remain the original value? Thanks a bunch!!

Re: Need help with importing

ScottBass — Mon, 16 Dec 2019 22:56:07 GMT

https://documentation.sas.com/?docsetId=acpcref&docsetTarget=p0jf3o1i67m044n1j0kz51ifhpvs.htm&docsetVersion=9.4&locale=en

https://documentation.sas.com/?docsetId=acpcref&docsetTarget=n0msy4hy1so0ren1acm90iijxn8j.htm&docsetVersion=9.4&locale=en

See MIXED option, then use a subsequent data step to convert "real" numeric data to numbers.

Re: Need help with importing

Reeza — Mon, 16 Dec 2019 23:08:26 GMT

Is your original file XLSX? Or XML? I would consider converting it to CSV and importing that, though if you have quotes anywhere in there it may end up more messy.

Re: Need help with importing

ballardw — Mon, 16 Dec 2019 23:56:49 GMT

Yet another lesson about the dangers of using spreadsheets to enter data and transport.

You have a very big clue as to what is going on in the picture. Note that some of the cells have values that are right justified like 99,100. If you check the cell format in Excel you will likely find that it is Number with the thousands separator set to comma.

The other cells did not do that because there were not 3 digits after the comma (so not valid numbers) or appeared within the brackets. The one with 161, 162, 163 also did not convert to numeric because of the space after the comma.

The cell with the value of 68 is likely of "General" format

If the data was actually entered directly into the spreadsheet then that is a part of the issue: allowing Excel to guess what you meant.

If the data was created somewhere and then opened and Saved by Excel that could modify values depending on the source file type.

SAS will only allow a variable to have a single type, numeric or character. Depending on the actual contents of the cell then the import engine will make "best guess" as to contents.

I would also check to see that you have "complete" values for records further down in the data file. You didn't say exactly how you are attempting to read this file but if using Proc Import then properties could be set with examining only 20 rows of data and if you have a value like [1234,1235,1236,1237] it may get truncated because none of the first rows had a value that long.

If this file started out as exported from some program and then was edited in Excel you may have to go back to that original file and try importing or reading a version that was not edited by Excel.

Re: Need help with importing

YimingWeng — Tue, 17 Dec 2019 15:01:47 GMT

Hi,

Thanks for your reply, this was one thing that i tried but i got this error message:

Anything i did wrong?

Re: Need help with importing

YimingWeng — Tue, 17 Dec 2019 15:03:24 GMT

I was wondering if you will see my post and reply:)

Original file is google sheet actually, i downloaded as xlsx.

I converted as csv and it works well, thanks so much Reeza!

Re: Need help with importing

YimingWeng — Tue, 17 Dec 2019 15:10:41 GMT

Thanks so much for the in-depth explanation for excel formatting.

The data is from a google app, IT created a google sheet for me as export, I then download as xlsx format. In terms of input restriction, it is almost like excel because front users can enter whatever in that field.

I tried Proc Import, not working, so I followed Reeza's tip to convert into csv and do data infile, now it's working, i just need to define all columns which is a little bit of work.

Ideally I should be connecting to the back end database directly to gain efficiency and accuracy in this matter, but I was told that SAS is not able to connect to GCP yet? So every week I am doing google sheet to xlsx to SAS data set conversion routine, lucky me:)

Again thanks for your help, at least now i have a temporary solution!

Re: Need help with importing

ScottBass — Wed, 18 Dec 2019 21:06:15 GMT

You can use Powershell and the Microsoft.ACE.OLEDB.12.0 provider to query Excel like a database (which it most certainly is NOT!)

You can then call the Powershell script from SAS using filename pipe, streaming the results as CSV. The Excel workbook must be closed at the time or you'll get file locking issues.

You can use PROC IMPORT against that CSV to generate a data step, then cut-and-paste the results from the SAS log and edit as required.

Here's hoping the structure of your Excel file does not change over time.

See here for the Powershell script: https://github.com/scottbass/Powershell/blob/master/Scripts/Query-Excel.ps1

Hope this helps a bit...

Re: Need help with importing

YimingWeng — Fri, 20 Dec 2019 16:10:31 GMT

Thanks so much for the detailed solution, I will follow your instruction and learn:)