Comparing Observations by looping through 2 variables at a time

[DrMD]

I have a dataset that has the following vars - Account_Id, Position_1123 to Position_0224
(1123 refers to Nov'23, 1223 refers to Dec'23 and so on)
Each of the positions have values ., 1,2,3
I am trying to find movements in positions month-on-month beginning here with Dec'23 (Position_1223) to Feb'24 (Position_0224).
Each month is compared to previous month.

Logic :-
1. If current month = last month and both are not ., then 'Static'
2. If current month <> last month and both are either 1,2,3, then 'Moved'
3. If current month = . and last month was either 1,2,3, then 'Closed'
4. If current month is either 1,2,3 and last month was ., then 'New'

Code:-

data test;

input id $ Position_1123 Position_1223 Position_0124 Position_0224;

datalines;

A1 1 1 1 1

A2 1 1 2 2

A3 1 2 2 2

A4 . 1 1 2

A5 2 3 3 .

A6 2 2 3 3

A7 . . 1 2

run;

data want;

set test;

format Position_mvmt $20.;

array Positions {*} Position_1123 Position_1223 Position_0124 Position_0224;

do i = 2 to dim(Positions);

if Positions[i] = Positions[i-1] and Positions[i] in (1,2,3) then Position_mvmt = 'Static';

else if Positions[i] <> Positions[i-1] and Positions[i] in (1,2,3) and Positions[i-1] in (1,2,3) then Position_mvmt = 'Moved';

else if Positions[i] = . and Positions[i-1] in (1,2,3) then Position_mvmt = 'Closed';

else if Positions[i] in (1,2,3) and Positions[i-1] = . then Position_mvmt = 'New';

else Position_mvmt = 'Other';

end;

drop i;

run;

Limitations:-
The code above compares and prints only the last 2 variables.
I want a method where it compares 1223 to 1123 and prints to Position_mvmt_1223 and then,
compare 0124 to 1223 to Position_mvmt_0124 and so on

[LinusH]

Please provide a WANT dataset based on your TEST dataset input.

[DrMD]

Hi, Please refer to the Excel that shows this. 
I might have got the loop right but not able to get the following variables :- 

Position_Mvmt_1223

Position_Mvmt_0124

Position_Mvmt_0224

What should I do to loop and print (this means replacing Position_mvmt in the existing code) ?

[PaigeMiller · Accepted solution]

This is close to what you asked for. You can assign formats to the MVMT variables to contain the exact text you want.

data want;
    set test;
    array pos pos:;
    array mvmt(3) $;
    do i=1 to dim(pos)-1;
        mvmt(i)=cats(pos(i),pos(i+1));
    end;
    drop i;
run;

I add that the layout of your data, with calendar information in the variable name, is something that you should particularly avoid, as this makes programming difficult.

I transpose the data to a long format to show what a long format data set looks like, this is really the way your data should be arranged, with calendar information as the values of variable ym.

proc transpose data=test out=transp;
    by id;
run;
data long;
    set transp;
    m=input(substr(_name_,10,2),2.);
    y=input(substr(_name_,12,2),2.);
    ym=mdy(m,1,2000+y);
    format ym yymmn6.;
    drop m y _name_;
run;

If you have the data in the transposed (long) form, then no looping is needed. Starting with long, the code is simple.

data want;
    set long;
    by id;
    lag_col1=lag(col1);
    if not first.id then mvmt=cats(lag_col1,col1);
    drop lag_col1 _name_;
run;

Now you have all your data and calculations in one data set that can be used easily. If necessary you can assign a format to the mvmt variable so it appears as the text you want.

You want a table output? No problem with PROC REPORT

proc report data=want;
    columns ("ID" id) ("Position" col1),ym ("Movement" mvmt),ym dummy;
    define id /' ' group;
    define col1/ ' ';
    define ym/' ' across;
    define mvmt/' ';
    define dummy/noprint;
run;

One other point: your dates are mmyy, this again is a particularly poor way to represent calendar dates, SAS and also humans work much better with dates that are yymm.

[DrMD]

Thank you very much. 

Great work and idea!