Help using Base SAS procedures

Modifying the length of variables in a dataset

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Modifying the length of variables in a dataset

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".


Accepted Solutions
Solution
‎05-21-2014 02:01 AM
Valued Guide
Posts: 3,208

Re: Modifying the length of variables in a dataset

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. 

---->-- ja karman --<-----

View solution in original post


All Replies
Solution
‎05-21-2014 02:01 AM
Valued Guide
Posts: 3,208

Re: Modifying the length of variables in a dataset

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. 

---->-- ja karman --<-----
Contributor
Posts: 71

Re: Modifying the length of variables in a dataset

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

Super User
Posts: 9,682

Re: Modifying the length of variables in a dataset

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

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 208 views
  • 1 like
  • 3 in conversation