1

I have 8 tables, all containing the same order and number of columns, while one specific column named ATTRIBUTE contains different data which is of length 4 to 25. When I use PROC SQL and UNION ALL tables, the ATTRIBUTE column data length in minimizes to the lowest (4 digits). How do I solve that i.e keeping full length of the data ?

Eyal Marom
  • 281
  • 4
  • 18
  • 2
    Normally PROC SQL uses the maximum length from all datasets so the length should be 25, but the format is keep from the first dataset so this why you see less characters. (Or digits when it is an numeric variable but than an length of 25 wouldn't work either) – Lee Jan 06 '20 at 12:20
  • I just used, like you said, another dataset as the first one in the UNION ALL which set the length for the rest of the tables. thanks – Eyal Marom Jan 06 '20 at 13:11
  • That was not exactly was i saying. It sets only the FORMAT! – Lee Jan 06 '20 at 14:19
  • either way - it worked – Eyal Marom Jan 06 '20 at 15:00

1 Answers1

0

Example, per @Lee

data have1;
  attrib name length=$10 format=$10.;
  name = "Anton Short";
run;

data have2;
  attrib name length=$50 format=$50.;
  name = "Pippy Longstocking of Stoyville";
run;

* column attributes such as format, informat and label of the selected columns
* in the result set are 'inherited' on a first found first kept order, dependent on 
* the SQL join plan (i.e. the order of the tables as coded for the query);

proc sql;
  create table want as
  select name from have1 union
  select name from have2
  ;

proc contents data=want varnum;
run;

Format is shorted than Length, any output display of longer values will appear to have been truncated at the data level.

enter image description here

* attributes of columns can be reset,
* (cleared so as to be dealt with in default manners),
* without rewriting the entire data set;

proc datasets nolist lib=work;
  modify want;
  attrib name format=;  * format= removes the format of a variable;
run;

proc contents data=want varnum;
run;

enter image description here

Richard
  • 25,390
  • 3
  • 25
  • 38