combine variable of different length from many tables

Reply
Regular Contributor
Posts: 161

combine variable of different length from many tables

Dear All:

I have around 50 tables that I hope to combine into one using:

data data_combo;

set data1-data50;

run;

These tables contain common variable names.  But I got the following error message:

Multiple lengths were specified for the variable VARIABLEXXX by input data set(s). This may cause truncation of data.

How could I avoid such problem by just specify in the data_combo, the length of the variable is set as the largest of the all the tables that are going to be combined into DATA_COMBO?  Right now, I have to do a PROC CONTENTS to see the variable length for all of the table and then manually specify the maximum length

Respected Advisor
Posts: 3,777

Re: combine variable of different length from many tables

/*Sample data*/
data data1 data3;
   length a $10 C $14;
  
stop;
  
call missing(of _all_);
   run;
data data2 data4;
   length a $15 x 8 b $13 c $13;
  
stop;
  
call missing(of _all_);
   run;

/* Collect relevant meta data*/
data info;
   set sashelp.vcolumn(keep=libname memname type name length);
   where libname eq 'WORK' and upcase(memname) eq: 'DATA';
   name = upcase(name);
  
run;

proc summary data=info nway;
  
class name / order=data;
   class type;
   output out=maxl(drop=_: index=(name)) max(length)=;
   run;
proc print;
  
run;

/* Gen some new attrib statements */
filename FT45F001 temp;
data _null_;
  
file FT45F001;
   set maxl;
   put 'Attrib ' name  'length=' @;
   if type eq 'char' then put '$' @;
   put length ';';
  
run;
data new;
   %inc FT45F001;
   set data1-data4;
   run;
proc contents;
  
run;
Regular Contributor
Posts: 161

Re: combine variable of different length from many tables

Suppose that the data are stored in d:\sasdata\ and the libname for it is mydata.  How would I accommodate the code? 

I tried to modify the code into:

where libname eq 'mydata' and upcase(memname) eq: 'DATA';

But it doesn't seem to work...

Super Contributor
Posts: 1,636

Re: combine variable of different length from many tables

Hi,

change

where libname eq 'mydata' and upcase(memname) eq: 'DATA';

to

where libname eq 'MYDATA' /* has to be upcase */ and upcase(memname) eq: 'DATA';


Message was edited by: Linlin

Respected Advisor
Posts: 3,124

Re: combine variable of different length from many tables

Alternatively if go for Proc SQL route:

proc sql;

  select distinct cats(upcase(name),'$', max(LENGTH)) into :len separated by ' ' from dictionary.columns

where LIBNAME='WORK'

AND SUBSTR(MEMNAME,1,4)='DATA'

AND type='char'

group by upcase(name);

QUIT;

data want;

length &len;

set data1-data4;

run;

Haikuo

Update: this only works for Char vars. While I have never seen a different length for Num other than 8 in my real work.

Regular Contributor
Posts: 161

Re: combine variable of different length from many tables

Hi, Hai:

I modify the libname = 'mydata', but the program report:

Syntax error, expecting one of the following: a name, ;, DEFAULT, _ALL_,

              _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 200-322: The symbol is not recognized and will be ignored

Respected Advisor
Posts: 3,124

Re: combine variable of different length from many tables

libname = 'MYDATA', needs CAPS.

Ask a Question
Discussion stats
  • 6 replies
  • 279 views
  • 1 like
  • 4 in conversation