topic Re: How do I avoid truncation when combining datasets? in SAS Programming

How do I avoid truncation when combining datasets?

HenF — Fri, 29 Apr 2022 08:37:09 GMT

I want to combine a number of datasets and each dataset contains a variable with the name of that dataset for every observations (no missing values).
No matter what I do the variable gets truncated in the combined dataset.

I have tried increasing the length to 100 in each dataset before joining them.
I have tried setting length again before the set statement in a data step.
I have tried joining the tables using outer union corr in proc sql.

Can anyone help me work out why this variable still gets truncated?

Example code:

data want;
length str $100.;

set a b c;

run;

proc sql ;

create table want as

select * from a

outer union corr

select * from b

outer union corr

select * from c

outer union corr

;quit;

Re: How do I avoid truncation when combining datasets?

Kurt_Bremser — Fri, 29 Apr 2022 08:58:14 GMT

Once the values have been truncated, you won't get them back. Anything that is present in the datasets will show up in the result (up to the newly defined length of 100).

Caution must be taken with the presence of formats in the incoming datasets. Make sure that no format is assigned to the variable.

data want; 
length str $100; 
set a b c; 
format str;
run;

Re: How do I avoid truncation when combining datasets?

GraphGuy — Fri, 29 Apr 2022 11:45:43 GMT

If you're allowed to modify the original datasets, something like this might work ...

data a;
length str $100.;

set a;

run;

data b;
length str $100.;

set b;

run;

data c;
length str $100.;

set c;

run;

proc sql ;

create table want as

select * from a

outer union corr

select * from b

outer union corr

select * from c

outer union corr

;quit;

Re: How do I avoid truncation when combining datasets?

HenF — Fri, 29 Apr 2022 18:30:10 GMT

The datasets all had sufficient lengths for that variable, the problem was caused by formats.

Removing them solved the problem.

Re: How do I avoid truncation when combining datasets?

HenF — Fri, 29 Apr 2022 18:31:17 GMT

The problem was caused by formats, but thanks!