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!
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!
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.
Ready to level-up your skills? Choose your own adventure.