DATA Step, Macro, Functions and more

Renaming variables

Reply
N/A
Posts: 0

Renaming variables

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.
Respected Advisor
Posts: 4,173

Re: Renaming variables

Posted in reply to deleted_user
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
Super Contributor
Super Contributor
Posts: 3,174

Re: Renaming variables

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.
N/A
Posts: 0

Re: Renaming variables

Posted in reply to deleted_user
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 Smiley Happy.
Respected Advisor
Posts: 3,799

Re: Renaming variables

Posted in reply to deleted_user
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_;
  • Ask a Question
    Discussion stats
    • 4 replies
    • 612 views
    • 0 likes
    • 4 in conversation