topic have problems reading in an excel sheet in SAS Programming

have problems reading in an excel sheet

rykwong — Tue, 21 Jun 2016 20:58:49 GMT

Dear SAS communities,

appreciate if anyone can help with my problem below:

when I import this excel using this code below, a large number of extra variables (ak--fi) with no data were created on the far right of the dataset. How can I import but not have these extra fields because they create conflict when merging this with other datasets.

PROC IMPORT Out=work.mrihemo1 DATAFILE= "\\.psf\home\Documents\IronKey\CMRCOOP_merge\1CMR_COOP\Data_excel\1_mri_hemo_responses.xlsx" DBMS=xlsx REPLACE; GETNAMES=YES; RUN;

have problems reading in an excel sheet

rykwong — Tue, 21 Jun 2016 20:58:35 GMT

Dear SAS communities,

appreciate if anyone can help with my problem below:

PROC IMPORT Out=work.mrihemo1 DATAFILE= "\\.psf\home\Documents\IronKey\CMRCOOP_merge\1CMR_COOP\Data_excel\1_mri_hemo_responses.xlsx" DBMS=xlsx REPLACE; GETNAMES=YES; RUN;

Re: have problems reading in an excel sheet

Ksharp — Wed, 22 Jun 2016 01:11:37 GMT

Use RANGE option:

PROC IMPORT Out=work.mrihemo1 DATAFILE= "\\.psf\home\Documents\IronKey\CMRCOOP_merge\1CMR_COOP\Data_excel\1_mri_hemo_responses.xlsx" DBMS=xlsx REPLACE;

GETNAMES=YES;

RANGE='Sheet1$A1:E10000' ;

RUN;

Re: have problems reading in an excel sheet

RW9 — Wed, 22 Jun 2016 08:46:31 GMT

The problem is caused by (and your going to be surprised here) Excel. If those cells outside your working area have any kind of information in them at all - and this includes formatting and styles etc. then Excel as default uses the whole range with any information. When another application tries to read Excel, they pull this maximum range out and use that for import. Fixing the Excel document would be my first goal, if there really is nothing in those additional columns, select them all and delete them, this should remove the problem. Alternatively use a proper data transfer format such as CSV or XML.

Do note, posting Excel files is not a good idea, I personally would not download such things from the internet.

Re: have problems reading in an excel sheet

rykwong — Mon, 11 Jul 2016 18:48:19 GMT

many thanks for your help!

I can manually clean the excel sheets to remove the columns (on the right of the sheet) that do not contain any data, but I was hoping that there may be a sas code that can auto delete these columns on the right. The reason is each time I received this sheet the empty columns on the right may be under different labels and it is painstaking to manually delete these columns each time.

Re: have problems reading in an excel sheet

rykwong — Mon, 11 Jul 2016 18:49:45 GMT

many thanks for your help!

I can manually clean the excel sheets to remove the columns (on the right of the sheet) that do not contain any data, but I was hoping that there may be a sas code that can auto delete these columns on the right. The reason is each time I received this sheet(s) the empty columns on the right may be under different labels and it is painstaking to manually delete these columns each time.