topic Re: automatic update of variable attributes in SAS Programming

automatic update of variable attributes

cbsxb — Mon, 11 Apr 2011 19:32:16 GMT

Hello, need your help.

I have two datasets (A and B) which have same variable names but different length and type. Periodically, I need to add B records to A. How can I automatically update B variable attributes to the same of A? Thanks.

Re: automatic update of variable attributes

ArtC — Mon, 25 Apr 2011 05:42:57 GMT

this post went unanswered from a coupleof weeks ago. Did you get the solution that you needed? If not are either of your data sets of a size that allow pre processing?

Re: automatic update of variable attributes

Ksharp — Mon, 25 Apr 2011 11:06:37 GMT

Hi.
ArtC.I only solve part question ,namely only make all variables in A and B to have the same length.

[pre]
data a;
set sashelp.class;
keep name sex weight;
run;
data b;
length name $ 50 sex $ 20 weight 3;
name='Peter'; sex='M'; weight=56.3; output;
name='Patrick'; sex='M';weight=56.2;output;
name='ArtC'; sex='M'; weight=69.9;output;
run;

%let table_a=A;
%let table_b=B;

%macro alter;
proc sql;
select cat( name ,case (type)
when ('char') then cat(' char(',length,')')
when ('num') then cat(' ',length)
end )
into : change separated by ','
from dictionary.columns
where libname='WORK' and memname="&table_a"
;

alter table &table_b
modify &change ;
quit;
%mend;

%alter

[/pre]

Ksharp

Message was edited by: Ksharp

Re: automatic update of variable attributes

Patrick — Tue, 26 Apr 2011 04:01:32 GMT

Hi
A similar approach but without the need to re-write the table.

data a;
attrib
key label='this is the key & nothing else'
a format=8. label='test & test';
do key=1 to 5;
a=1;
b='abc';
c=1;
output;
end;
run;

data b;
do key=6 to 10;
a=1;
b='abcd';
d=5;
output;
end;
run;

%let rename=;
%let assign=;
%let length=;
%let drop=;
proc sql noprint;
select
cats(BASE.name,'=xyz_',BASE.varnum)

,cats(BASE.name,'=xyz_',BASE.varnum,';')

,case
when BASE.type='char' then
catx(' ',BASE.name,cats('length=$',BASE.length))
else catx(' ',BASE.name,cats('length=',BASE.length))
end

,cats('drop xyz_',BASE.varnum,';')

into :rename separated by ' '
, :assign separated by ' '
, :length separated by ' '
, :drop separated by ' '

from dictionary.columns BASE, dictionary.columns DATA

where upcase(BASE.name)=upcase(DATA.name)
and base.length ne data.length
and BASE.libname='WORK' and BASE.memname='A'
and DATA.libname='WORK' and DATA.memname='B'

order by BASE.varnum
;
quit;

%put rename: &rename;
%put assign: &assign;
%put length: &length;
%put drop: &drop;

data V_B / view=V_B;
set B(rename=(&rename));
attrib &length;
&assign
&drop
run;

proc append base=A data=V_B force nowarn;
quit;

Re: automatic update of variable attributes

Ksharp — Tue, 26 Apr 2011 09:43:28 GMT

Hi.Patrick
My code is also not to re-write dataset, it is directly to modify the attribute of variables.
It is good to see you for a long time not to see your post.
Is it still too busy to post?

Cheers
Ksharp

Re: automatic update of variable attributes

Patrick — Wed, 27 Apr 2011 08:04:04 GMT

Hi Ksharp

Yes, unfortunately (?) I've been really busy lately.

And: I still think that changing the length of a variable needs re-creating the SAS file.

Length is not a simple variable attribute like a format or a label. I assume that's also the reason why you can't change it with Proc Datasets the way you change other attributes.

Considering that SAS stores data rectangular the SAS file must be re-created in order to change the variable length (a view kind of also re-creates the table in memory).

Just run the following code and compare the file size of test1 and test2 and you will see:

options compress=no;

libname test 'c:\temp';

data test.test1 test.test2;
length var $ 10;
do i=1 to 1000;
var=put(i,8.);
output test.test1;
output test.test2;
end;
run;

proc sql;
alter table test.test2
modify var char(1000);
;
quit;

Cheers
Patrick Message was edited by: Patrick

Re: automatic update of variable attributes

Peter_C — Wed, 27 Apr 2011 08:12:23 GMT

nothing seems to solve this so far, so:
data B ;
* load B with the type+length attributes of A ;
if 0 then set A ;
* this will load ALL columns of A as well (not just those in B) ;

* load values from B ;
set B ;
run ;

* and add B records to A;
proc append base= A data= B force ;
run;
The data step reloading B, will get error[pre]
ERROR: Variable XXXXXXX has been defined as both character and numeric[/pre] if you have a variable on A and B with different types.

Character data in B will truncate if A lengths are not wide enough to hold B strings!

Similarly, if numeric columns in A have shorter (stored) lengths than corresponding columns in B, numeric precision will be affected!

CBSXB
To avoid the "ERROR"-condition above, what are your rules for conversion between character and numeric data types?
Do you need special handling when $lengths in A are not wide enough for corresponding columns in A?

peterC

Re: automatic update of variable attributes

Patrick — Wed, 27 Apr 2011 11:27:09 GMT

Hi Peter

I was very aware that the code I suggested doesn't handle character/numeric missmatch - and it's on purpose that my code will throw an error for such a case(enough is enough...).

Also on purpose I've set up the example in a way that strings get truncated - just to show that may be modifying variable lengths is not really the solution to the problem.

I believe what one should do is to define appropriate variable lengths explicitly (attrib statement) so length matching issues won't occur at all.

The amendment to the code I've posted could be to compare variable lengths between base and transaction data set and change the length in the data set where it's shorter.

Cheers
Patrick

Re: automatic update of variable attributes

Peter_C — Wed, 27 Apr 2011 14:10:10 GMT

Patrick
It's easy to agree with you.
I might go further than enter a sensible policy discussion.
The original poster requested techniques to solve what looks like a real problem when generalised. However, we don't know enough about the environment of the problem - perhaps it is simpler than the original question implies (to us).
It would be good to hear more from that poster.

Regards
Peter

Re: automatic update of variable attributes

Ksharp — Thu, 28 Apr 2011 00:51:05 GMT

Hi.
Patrick.
I also notice this interesting thing that proc datasets can not change the length of variable,
But proc sql can do really,Is it strange?.
If you use proc contents or see the attribute of dataset ,then you will see the length of variable is really change after sql.
But data new; set old; attrib ...; will really load dataset once ,it yields to low efficient.

Ksharp Message was edited by: Ksharp

Re: automatic update of variable attributes

Ksharp — Thu, 28 Apr 2011 00:54:50 GMT

Dear Peter:
I also think the biggest challenge is to change variable type between two tables.
So, I think it might will be more easy that firstly changing all the variables type into character, after proc append then change the variable type which is numeric originally in dataset A. How do you think?

Ksharp
Cheers from BeiJing ,China

Re: automatic update of variable attributes

Patrick — Thu, 28 Apr 2011 10:44:16 GMT

Hi Ksharp

But this is exactly the point: Also SQL alter table must re-create the SAS file.

Length is just more than only a simple variable attribute in the descripter part. The length directly connects to how data gets stored - so changing the length just must result in re-writing the data part whatever SAS syntax is used.

Cheers
Patrick

Re: automatic update of variable attributes

Peter_C — Thu, 28 Apr 2011 19:11:09 GMT

interesting thought occurs.

SAS stores data as we request it (having requested formats, lengths, compression, etc.).

to be used in a PROC or a data step, short length numerics are expanded to length 8. When we request compression, the storage must be expanded to be used.

New string lengths, could "be requested".
It would be similar to "requesting a new column order".
No data moves (except in data set header) until the data portion is actually used.
The original data would not be affected - just new data derived from the old data would be subject also to these "requests"
These "requests" could be made with PROC DATASETS.

well that is just in my imagination - it is not what happens - as we know it for real!

peterC SAS already achieves this and more! The technology is not in PROC DATASETS - it's referred to as DATA set VIEW!
very little not already built in!
peterC 07:55 29-Apr-11.
Message was edited by: Peter.C

Re: automatic update of variable attributes

Ksharp — Fri, 29 Apr 2011 01:26:28 GMT

Hi.Patrick.
It looks like You have more insight than me .But I have not found something about it in documentation. Education!
:)
Ksharp

Re: automatic update of variable attributes

LeonidBatkhan — Thu, 14 Sep 2017 15:06:14 GMT

This scenario is described in a blog post Changing variable type and variable length in SAS datasets where you can find SAS code example on how to change variable attributes of a SAS data table such as type and length. It seems to be a common problem when appending SAS data tables with slighty different column attributes.

Re: automatic update of variable attributes

Ron_MacroMaven — Thu, 14 Sep 2017 20:16:05 GMT

This is a non-trivial problem.

note.1: where I changed the length of a known variable with the attribute statement

and the var-name does not match the case of the master data set

then that var-name is changed.

Since is this is a demo of proof-of-concept

this may not matter.

note.2 be aware of the fragile nature of the cat* functions;

if they return a value larger than $200 then it will truncate your list

solution: add a length=nnnn after the catx function call

note.3 data structure may be only <1% of your code

but it is the first place I turn to when my algorithm fails.

Likewise here: I recommend adding the data structure of A

to the code that creates B; this is my recommended solution and solves all later appending.

DATA B;
if 0 then set A;
*...;

note.4 this solution assumes both data sets are different

and thus have to be copied in order to change the lengths.

hth

Ron Fehd list processing maven

*note case of name matters in output;
data class1;
     attrib sex length = $2;
set sashelp.class;

data class2;
     attrib name length = $10;
set sashelp.class;

%macro save_attrib(libname=work, memname=,out=);
PROC sql noprint;
         create table &out as
         select name, lowcase(name) as name_lc, type, length
         from dictionary.columns
         where libname eq "%upcase(&libname)"
           and memname eq "%upcase(&memname)"
           and memtype eq "DATA";
         quit;
run;
%mend;
%save_attrib(memname=class1,out=contents1)
%save_attrib(memname=class2,out=contents2)

DATA attributes;
set contents1
    contents2;
run;
PROC sort data = &syslast;
          by name_lc length;
run;
DATA attributes;
     retain max_length 0;
set  &syslast;
by   name_lc;
if first.name_lc then max_length = 0;
max_length = max(max_length,length);
if last.name_lc then output;
run;
PROC sql noprint;
         select catx(' ',name,'length='
         ,case when type eq 'char' then '$'
               else                     ' '
               end
         ,length) into :list separated by ' '
         from &syslast;
         quit;
%put echo &=list;
%macro update_attrib(data=,base=largest);
DATA &DATA;
     attrib &list;
set &data;
PROC append data = &data
            base = &base;
run;
%mend;
%update_attrib(data=class1)
%update_attrib(data=class2)

proc print data = &syslast;

PROC sql; describe table &syslast;
          quit;