topic Re: data truncation while importing and merging excel sheet in SAS Procedures

data truncation while importing and merging excel sheet

buckeyefisher — Mon, 05 Oct 2015 15:05:22 GMT

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*

Re: data truncation while importing and merging excel sheet

ballardw — Mon, 05 Oct 2015 15:20:00 GMT

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.

Re: data truncation while importing and merging excel sheet

buckeyefisher — Mon, 05 Oct 2015 15:46:22 GMT

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;

Re: data truncation while importing and merging excel sheet

Reeza — Mon, 05 Oct 2015 15:52:05 GMT

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

Re: data truncation while importing and merging excel sheet

buckeyefisher — Mon, 05 Oct 2015 15:53:22 GMT

Reeza,

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

Re: data truncation while importing and merging excel sheet

Reeza — Mon, 05 Oct 2015 15:59:14 GMT

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.

Re: data truncation while importing and merging excel sheet

buckeyefisher — Mon, 05 Oct 2015 18:15:06 GMT

Reeza- Are suggesting this

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

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

Re: data truncation while importing and merging excel sheet

ballardw — Mon, 05 Oct 2015 19:30:23 GMT

@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;

Re: data truncation while importing and merging excel sheet

Reeza — Mon, 05 Oct 2015 19:38:32 GMT

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;