BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
4 REPLIES 4
Patrick
Opal | Level 21
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
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 :).
data_null__
Jade | Level 19
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_;
  • sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    How to Concatenate Values

    Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 4 replies
    • 1919 views
    • 0 likes
    • 4 in conversation