data truncation while importing and merging excel sheet

[buckeyefisher]

I am  using following macro to import and combine 40 excel sheet. But some of the fields are getting truncated. Is there a way to avoud truncation in the code below? 

options mprint mlogic symbolgen;
%macro merge(n);*n is the number of your xls files;
%do i=1 %to &n;
proc import out=datafile&i datafile="&&file&i" dbms=xls replace;
sheet="XYZ";
getnames=yes;
run;

run;
%end;

data whole;
set %do j=1 %to &n;
datafile&j
%end;
;
run;

%mend merge;

filename lib pipe 'dir "ABC\*.xls" /b';
data _null_;
infile lib;
input;list;
run;

data file;
length filenames $ 140;
infile lib truncover;
input filenames : $;
filenames="ABC\"||filenames;
run;

data null;
set file;
call symputx('file'||put(_n_,8. -L ),filenames,'G');
run;
options sasautos=work;
%merge(40) * there are 40 excel sheets that need to imported and combined*

[ballardw]

You may need to use Options MPRINT to see where this comes up but I suspect you are getting some message in the log about variables having different lengths. The place is most like in the step for DATA whole as the lengths of the variables from the first data set in the set statement are setting the lengths.

Because you are reading the data with proc import Excel is telling SAS how long the variables are (and what type as well) and likely varies for each data set created by proc import. This is easy to check with Proc Contents on those data sets.

If you are are going to use proc import you may want to make a dummy data set with no observations with the length and other attributes (format, label) for all of the variables. Then use that as the first data set referenced in the SET statement.

Or go to a method that reads the data in a more controlled manner such as a data step to read the Excel data saved as CSV.

[buckeyefisher]

Ballardw,

I tried defining a variable in the output dataset  -  but still the same problem.

%macro merge(n);*n is the number of your xls files;
%do i=1 %to &n;
proc import out=datafile&i datafile="&&file&i" dbms=xls replace;
sheet="DrugExport";
getnames=yes;
run;

run;
%end;

data whole;
length 'fieldX'n $950;

set %do j=1 %to &n;
datafile&j
%end;
;
*if (prxmatch("m/pfizer/oi",('Originator Company'n)))> 0 or (prxmatch("m/pfizer/oi",('Active Companies'n))) > 0;
run;

%mend merge;

[Reeza]

Is Excel truncating the field on the import or are you getting truncation after appending the files?

[buckeyefisher]

Reeza,

My individual import files look fine. Truncation hapend when I merge them. 

[Reeza]

Then applying a length and format ahead of the SET should remove the truncation. Verify the format is also set properly otherwise it may appear truncated when the data is actually there.

[buckeyefisher]

Reeza-  Are suggesting this 

data whole;
format 'fieldX'n  $ 950. ;

set %do j=1 %to &n;
datafile&j
%end;

[ballardw · Accepted solution]

---

@buckeyefisher wrote:

Reeza-  Are suggesting this 

data whole;
format 'fieldX'n  $ 950. ;

set %do j=1 %to &n;
datafile&j
%end;

---

Make sure LENGTH AND FORMAT agree.
data whole;
length 'fieldX'n $ 950 ;
format 'fieldX'n  $ 950. ;

set %do j=1 %to &n;
datafile&j
%end;

[Reeza]

It's probably faster to try rather than ask 😉

Also, if all your files start with datafile AND you don't have any other files that start with datafile you can just use:

data whole;
format field $950.; length field $950.;
set datafile:;
run;