BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
DrMD
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

View solution in original post

4 REPLIES 4
LinusH
Tourmaline | Level 20
Please provide a WANT dataset based on your TEST dataset input.
Data never sleeps
DrMD
Calcite | Level 5

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_1223Position_Mvmt_0124Position_Mvmt_0224

 

DrMD_0-1718707027893.png


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

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
DrMD
Calcite | Level 5

Thank you very much. 

Great work and idea!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1341 views
  • 1 like
  • 3 in conversation