topic Renaming variables in SAS Programming

Renaming variables

deleted_user — Mon, 06 Sep 2010 07:40:13 GMT

I'm trying to achieve, what I thought would be, a simple goal. I've got a data table with text variables named m0, m1 up to m62. Each month this dataset is updated, with the latest month's data stored in m0 while all the rest need to move up on index, i.e. m0 should be renamed m1, m1 should be renamed m2, etc. up to m62 which should be renamed m63.

What would be the best way to accomplish this in SAS? Any help will be much appreciated.

Re: Renaming variables

Patrick — Mon, 06 Sep 2010 09:57:41 GMT

Hi

Hope the code below will give you the idea:

data previous_all_months (drop=i);
length id 8;
array m {64} 8 m0 - m63;
do id=1 to 2;
do i= 1 to dim(m);
m(i)=i;
end;
output;
end;
run;

data new_month;
id=1;
m0=101;
output;

id=2;
m0=102;
output;

id=3;
m0=103;
output;
run;

proc sql;
/* create table current_all_months as*/
select
coalesce(p.id,n.id) as id
,n.m0
,p.m0 as m1
,p.m1 as m2
,p.m2 as m3
,p.m3 as m4
/* and so on until p.m62 as m63 */
from previous_all_months p full join new_month n
on p.id = n.id
order by id
;
quit;

Better would be to have the data organised in the following way:
ID, Date, Var.

Adding a new month would then simply be a Proc Append.
Getting rid of an old month would simply be a where clause 'where Date GT

HTH
Patrick

Re: Renaming variables

sbb — Mon, 06 Sep 2010 14:54:56 GMT

I agree with Patrick recommendation - convert your file to be stored "vertically" for maintenance/update/change and then consider a SAS view or another parallel-file technique to give the "horizontal" perspective. This would be similar to generating a PivotTable report output with time periods across the columns based on existing master file data that is stored as one observation period time-period.

Scott Barry
SBBWorks, Inc.

Re: Renaming variables

deleted_user — Mon, 06 Sep 2010 23:44:30 GMT

Thanks Patrick. I am new to SAS so it was extremely frustrating when I could not get this simple task done. Next time I will know what to do :).

Re: Renaming variables

data_null__ — Tue, 07 Sep 2010 13:50:01 GMT

You "can" do this by renaming, using descending ranges. Using descending ranges prevents renaming to a name that already exists.

Rename requires descending ranges to have the have leading zeros or you get this error.
[pre]
2587 merge trans current(rename=(m63-m0=m64-m1));
ERROR: Either roots don't match or start suffix after end suffix.
[/pre]

Which is fine because SAS allows us to rename a range to a new range with leading zeros then rename it again using the descending range with leading zeros. See example.

I also included a step to determine the largest M variable name, so the code would work from month to month.

You really should keep the data in tall form, but it's fun to see how rename works.

[pre]
data Trans;
do id = 1 to 3;
m0 = ranuni(1243);
output;
end;
run;
data Current;
do id = 1 to 2;
array m

m0-m24;
do _n_ = 1 to dim(m);
m[_n_]=_n_;
end;
output;
end;
run;
proc print;
run;
* Find max M variable;
proc sql noprint;
select max(input(substr(name,2),8.)) into :m
from dictionary.columns
where libname='WORK' and memname='CURRENT' and upcase(name) eqT 'M'
;
quit;
run;
%let M=&m;
%let P=%eval(&m+1);
%put NOTE: M=&m P=&p;

data new(/*drop=M&p*/);
merge trans
current
(
rename=
(
/* This is too complicated
m0-m&m = m0000-m00&m
m00&m-m0000 = m00&p-m0001
m0001-m00&p = m1-m&p
*/
m0-m&m = _0-_&m
_0-_&m = m1-m&p

)
);
by id;
run;
proc contents varnum;
run;
proc print;
run;
[/pre] Complex method replaced by simpler rename

Message was edited by: data _null_;