BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buckeyefisher
Obsidian | Level 7

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*

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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

8 REPLIES 8
ballardw
Super User

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
Obsidian | Level 7

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
Super User
Is Excel truncating the field on the import or are you getting truncation after appending the files?
buckeyefisher
Obsidian | Level 7

Reeza,

 

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

Reeza
Super User
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
Obsidian | Level 7

Reeza-  Are suggesting this 

 

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

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

ballardw
Super User

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 2767 views
  • 3 likes
  • 3 in conversation