have problems reading in an excel sheet

[rykwong]

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;

[rykwong]

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;

[Ksharp]

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;

[RW9]

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.

[rykwong]

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.  

[rykwong]

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.