Help using Base SAS procedures

data truncation while importing and merging excel sheet

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

data truncation while importing and merging excel sheet

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*

 

 


Accepted Solutions
Solution
‎10-06-2015 11:30 AM
Super User
Posts: 10,460

Re: data truncation while importing and merging excel sheet


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;

 

 

View solution in original post


All Replies
Super User
Posts: 10,460

Re: data truncation while importing and merging excel sheet

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.

Contributor
Posts: 63

Re: data truncation while importing and merging excel sheet

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;

Super User
Posts: 17,737

Re: data truncation while importing and merging excel sheet

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

Re: data truncation while importing and merging excel sheet

Reeza,

 

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

Super User
Posts: 17,737

Re: data truncation while importing and merging excel sheet

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.
Contributor
Posts: 63

Re: data truncation while importing and merging excel sheet

Reeza-  Are suggesting this 

 

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

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

Solution
‎10-06-2015 11:30 AM
Super User
Posts: 10,460

Re: data truncation while importing and merging excel sheet


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;

 

 

Super User
Posts: 17,737

Re: data truncation while importing and merging excel sheet

It's probably faster to try rather than ask Smiley Wink

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 460 views
  • 3 likes
  • 3 in conversation