topic Re: Modifying the length of variables in a dataset in SAS Procedures

Modifying the length of variables in a dataset

PTD_SAS — Wed, 21 May 2014 05:06:53 GMT

How can I modify the length of all variables in a dataset without having to recreate them? Can it be done with Proc Datasets?

I have an old and a new dataset with the same variable names but diffrent lengths, when I merge them I get the warning "Multiple lengths specified for the variable xxxx. This may cause truncation of data".

Re: Modifying the length of variables in a dataset

jakarman — Wed, 21 May 2014 06:01:17 GMT

You cannot change the length of and variable in an existing dataset.
That is one of the basics working with tables/RDBMS. Ask this to a DBA of a RDBMS and you will be see big eyes.

As you can manipulate tables very easy with SAS there is an other way. Understand the way the PDV works using the datastep.

What I mean is you are knowing what it should be, use that knowledge.

data <your newname> ;

length <var ..> ; /* set length-s as needed */

set/merge dsn1 dsn2 ; /* will use length as you have set yourself, not of the input datasets */
run;

Proc append is designed for appending it has assumptions in the same way, assuming the base dataset is leading.

Re: Modifying the length of variables in a dataset

PTD_SAS — Wed, 21 May 2014 06:10:46 GMT

I specified the length when I merged the datasets and it worked fine! Thank you...

Re: Modifying the length of variables in a dataset

Ksharp — Wed, 21 May 2014 12:34:31 GMT

Here is a convenient way if you only need to SET them together.

proc sql;

create table want as

select * from old

outer union corr

select * from new ;

quit;

And If you only want to change the length of variables :

data have1;
input name : $2. x $ id;
cards;
NB sds 1
RN sdft 1
;
run;
data have2;
input name $ x : $14. id;
cards;
NB sdsfsddfs 2
RN sdsdfsdfsder 3
BOTH sdsds 4
;
run;
/*First,we get the max length of variables in every tables */
proc sql;
create table temp as
select memname,name,max(length) as max_len
from dictionary.columns
where libname='WORK' and memname like 'HAVE%' and type='char'
group by name
order by memname,name;
quit;
/* then we use sql to change the variable's length fastly and easily.
we need to generate the sql code like:
proc sql;
alter table have1
modify name char(8),x char(14);
alter table have2
modify name char(8),x char(14);
.........
quit;
*/
data _null_;
set temp end=last;
by memname;
if _n_ eq 1 then call execute('proc sql;');
if first.memname then call execute('alter table '||memname||' modify ');
call execute(name||' char('||put(max_len,8.)||')');
if not last.memname then call execute(',');
else call execute(';');
if last then call execute('quit;');
run;
/*finally, get what we want */
data want;
set have1 have2 ;
run;

Xia Keshan