will vertical merge truncate data?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

will vertical merge truncate data?

Dear All:

I have a question on using the following data step to merge two table HAVE 1 AND HAVE2

data WANT (compress=yes);

    set HAVE1 HAVE2;

run;

The variables in HAVE1 AND HAVE2 are the same, but the length might be different.  In this case, would the merge truncate the data if I didn't specify length ex ante?  Thank you -


Accepted Solutions
Solution
‎12-15-2013 10:18 AM
Respected Advisor
Posts: 3,799

Re: will vertical merge truncate data?

Posted in reply to caveman529

Yes, but it is not "exceedingly" complex.  It does require some code gen and that can take many forms, and can be made exceedingly complex.  You also need to analyze the meta data (max length) of the data sets of interest, which requires that you make a SAS data set that contains the meta data you will need.  There is also an issue of FORMAT that was demonstrated in another thread.  I will just remove the formats from all the character variables in this example.

data x1;
   length c 8 a $1 b $4;
  
call missing(of _all_);
   run;
data x2;
   length a $2 b $3 c 4;
  
format b $3.;
  
call missing(of _all_);
   run;
data x3;
   length a $1 b $3 c 8;
  
call missing(of _all_);
   run;

proc contents
     
data=work._all_
     
out=columns
         (
           
keep=memname name type length varnum
           
where=(memname eq: 'X')
         )
     
noprint
      ;
   run;
proc summary data=columns nway nothreads;
  
class name type;
   output out=max(drop=_Smiley Happy idgroup(max(length) out(length varnum)=);
   run;
proc sort data=max;
   by varnum;
   run;
filename FT45F001 temp;
data _null_;
  
set max;
   file FT45F001;
   put +3 'Attrib ' name 'length=' @;
   if type eq 2 then put '$' @;
   put length ';';
  
run;
data new;
   %inc FT45F001 / source2;
   set x1-x3;
   format _char_;
   run;
proc contents varnum;
  
run;

View solution in original post


All Replies
Regular Contributor
Posts: 151

Re: will vertical merge truncate data?

Posted in reply to caveman529

The variable attributes are determined by the first dataset read in, so in your example HAVE1.  If the variable lengths in HAVE2 are longer then values would be truncated if they contain more characters than the length specified in HAVE1.

Super User
Posts: 11,343

Re: will vertical merge truncate data?

Which is exactly why you get warnings in the log about different length variables when they occur.

Regular Contributor
Posts: 161

Re: will vertical merge truncate data?

Thank you for your answer.  May I ask if there is a way to avoid the truncation by look at the variable from all source input tables and then determine the maximum length accordingly?  Seems to be an exceedingly complex task...

Solution
‎12-15-2013 10:18 AM
Respected Advisor
Posts: 3,799

Re: will vertical merge truncate data?

Posted in reply to caveman529

Yes, but it is not "exceedingly" complex.  It does require some code gen and that can take many forms, and can be made exceedingly complex.  You also need to analyze the meta data (max length) of the data sets of interest, which requires that you make a SAS data set that contains the meta data you will need.  There is also an issue of FORMAT that was demonstrated in another thread.  I will just remove the formats from all the character variables in this example.

data x1;
   length c 8 a $1 b $4;
  
call missing(of _all_);
   run;
data x2;
   length a $2 b $3 c 4;
  
format b $3.;
  
call missing(of _all_);
   run;
data x3;
   length a $1 b $3 c 8;
  
call missing(of _all_);
   run;

proc contents
     
data=work._all_
     
out=columns
         (
           
keep=memname name type length varnum
           
where=(memname eq: 'X')
         )
     
noprint
      ;
   run;
proc summary data=columns nway nothreads;
  
class name type;
   output out=max(drop=_Smiley Happy idgroup(max(length) out(length varnum)=);
   run;
proc sort data=max;
   by varnum;
   run;
filename FT45F001 temp;
data _null_;
  
set max;
   file FT45F001;
   put +3 'Attrib ' name 'length=' @;
   if type eq 2 then put '$' @;
   put length ';';
  
run;
data new;
   %inc FT45F001 / source2;
   set x1-x3;
   format _char_;
   run;
proc contents varnum;
  
run;
Regular Contributor
Posts: 161

Re: will vertical merge truncate data?

Posted in reply to data_null__

Respect...

I'll implement this code tomorrow first thing in the office.  :smileylaugh:

Respected Advisor
Posts: 3,156

Re: will vertical merge truncate data?

Posted in reply to caveman529

As an alternative to 's approach, here is another example only considering Character variables. In most cases, you should always have length 8 for numeric variables, if not, for instance you have inherited some legacy code from the era where hardware resources were very limited, then you can easily derive some code from the following example.

data have1;

  a='a';b='abc';

run;

data have2;

a='ac';b='a';

run;

proc sql noprint;

  select cats(name,'$',max(length)) into :len separated by ' '

     from dictionary.columns

         where libNAME='WORK' AND TYPE='char'

          GROUP BY NAME;

QUIT;

data want;

length &len;

  set have1 have2;

run;


Haikuo

Regular Contributor
Posts: 161

Re: will vertical merge truncate data?

Thank you -

I'll try both and report back on this.  Thank you so much, guys!  Smiley Happy

Super User
Posts: 5,518

Re: will vertical merge truncate data?

Posted in reply to caveman529

As a general rule, PROC SQL usually finds a way to select the longer length automatically.  You can always try it and see if it works in this case.

Regular Contributor
Posts: 161

Re: will vertical merge truncate data?

Posted in reply to Astounding

Sounds interesting.  Can PROC SQL merge data veritically?  I have only done it for horizontal merge... Thanks -

Respected Advisor
Posts: 3,156

Re: will vertical merge truncate data?

Posted in reply to caveman529

Yes, it does. It is called "UNION" instead. It is fairly straightforward (like most of the SQL features), do a search and you will have it all.

Haikuo

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 394 views
  • 5 likes
  • 6 in conversation