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:-
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
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.
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) ?
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.
Thank you very much.
Great work and idea!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.