topic Re: Trouble with IMPORT xlsx with starting line and variables names in SAS Procedures

Trouble with IMPORT xlsx with starting line and variables names

GuiVtzl — Thu, 22 May 2014 16:38:29 GMT

Hello everyone,

I have some trouble importing a table that looks that way :

I need to create a table SAS with data from line 4 AND get variables names on line 3. But I can't make it.

I tried :

proc import out=xll.input

datafile="/data/&input_drf."

dbms=xlsx REPLACE;

SHEET='Clients';

datarow=4;

run;

But it gets me "The SAS SYSTEM" "B" "C" etc as variables. Plus, the ID became of kind "4.3080236E12".

Then I tried to add GETNAMES=YES but it produced the very same result...

Could you please help me ? Thanks a lot

Re: Trouble with IMPORT xlsx with starting line and variables names

jakarman — Thu, 22 May 2014 17:37:04 GMT

The lay-out look to be a SAS report of a dataset displayed with AMO in Excel. Why would you try to convert a SAS dataset to a SAS dataset when the viewer/browser is just Excel.

Re: Trouble with IMPORT xlsx with starting line and variables names

GuiVtzl — Fri, 23 May 2014 07:22:51 GMT

Good morning Jaap,

in fact the reason is simple : I receive that excel as an input from another department in my company, and I can only receive it that way.

So, I absolutely need to import that excel file in SAS

Re: Trouble with IMPORT xlsx with starting line and variables names

jakarman — Fri, 23 May 2014 07:42:24 GMT

Understandable than that guy of the other department did used the AMO and has all that in a SAS-dataset.
He could have send you the SAS dataset. Now you are busy to recreate that one. In the same company corporate cooperation....

The most easy approach would be try to export the selected area you need as a CSV and use that. Not that much work and a datastep is coded fast.

The ID field is obviously having numbers but you could need to do that as chars. The numeric precision is not reliable above some 12 digits.
ID is for identification I assume something like custom number. Calculations as mean skewness not likely to be expected.

Re: Trouble with IMPORT xlsx with starting line and variables names

GuiVtzl — Fri, 23 May 2014 07:53:19 GMT

Yes that is incredible..And they do nothing to make the task easier.

Above all, I need to automatize a whole process (beginning with the import of that xlsx).

Well converting it to CSV make it not completely automatized but at least I can run the rest of my program...

I did convert it to CSV but for ID I have the same trouble, it becomes digits with E+12 types.

Re: Trouble with IMPORT xlsx with starting line and variables names

jakarman — Fri, 23 May 2014 08:12:23 GMT

Well you are still using it as number not char and there 13 digits. reading defining it as char (length eg $16) or using a format with 15 digits would help.
But you are at the end of the numeric precision with numbers, They could go off on the last low values. (sas note 123)