BookmarkSubscribeRSS Feed
rykwong
Quartz | Level 8

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;

 

 

 

 

5 REPLIES 5
rykwong
Quartz | Level 8

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Quartz | Level 8

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.  

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1314 views
  • 0 likes
  • 3 in conversation