CODEWITHSUNDEEP
sasunion-allproc-sql
Eyal Marom
Eyal Marom

Reputation: 301

SAS PROC SQL UNION ALL - minimizes column length

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 ?

Upvotes: 1

Views: 2264

Answers (1)

Richard
Richard

Reputation: 27498

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

Upvotes: 0

Related Questions