topic Re: combine variable of different length from many tables in SAS Programming

combine variable of different length from many tables

caveman529 — Sat, 21 Sep 2013 06:59:20 GMT

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

Re: combine variable of different length from many tables

data_null__ — Mon, 23 Sep 2013 20:31:59 GMT

/*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; 

Re: combine variable of different length from many tables

Haikuo — Mon, 23 Sep 2013 20:47:52 GMT

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.

Re: combine variable of different length from many tables

caveman529 — Tue, 24 Sep 2013 01:55:11 GMT

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...

Re: combine variable of different length from many tables

caveman529 — Tue, 24 Sep 2013 01:56:23 GMT

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

Re: combine variable of different length from many tables

Linlin — Tue, 24 Sep 2013 02:52:07 GMT

Hi,

change

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

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

Message was edited by: Linlin

Re: combine variable of different length from many tables

Haikuo — Tue, 24 Sep 2013 15:05:47 GMT

libname = 'MYDATA', needs CAPS.