How do I manipulate value of "next" observation in Column Cumulative New Tuition ( ? ?)
Student | Course | Course Rank | Tution | Cumulative Tuition (Calculated) | New Tution | Cummulative New Tution (Calculated) | Actual Total Tution Target - 1 | Flag1 | Actual Total Tution Target - 2 | Flag2 |
---|---|---|---|---|---|---|---|---|---|---|
123 | BIO | 2 | 900 | 900 | 1000 | 1000 | 2500 | Process | 2800 | Process |
123 | CHE | 2 | 200 | 1100 | 300 | 1300 | 2500 | Process | 2800 | Process |
123 | ENG | 3 | 700 | 1800 | 800 | 2100 | 2500 | Process | 2800 | Process |
123 | PHY | 3 | 500 | 2300 | 500 | 2600 | 2500 | CALC | 2800 | Process |
123 | MAT | 4 | 300 | 2600 | 300 | 2900 | 2500 | STOP | 2800 | CALC |
123 | BUS | 4 | 400 | 3000 | 400 | 3300 | 2500 | STOP | 2800 | STOP |
123 | COM | 5 | 500 | 3500 | 1000 | 4300 | 2500 | STOP | 2800 | STOP |
Total Tuition Fees =3500
Actual Total Tution Target1 = 2500
Actual Total Tution Target2 = 2800
Within Each Course rank, if NEW cumulative Target tuition fee is < Actual Targeted Tuition Fee, Populate Flag ="Process"
if NEW cumulative Target tuition fee is > Actual Total Tuition Fee then, populate Flag = "CALC"
else populate flag = "STOP"
I don't know how to populate Value "CALC" and "STOP" (below is my logic but it populates "CALC" for every record after Course "PHY"
Data test;
set test;
by student course course_rank;
array flag{2} flag1 - flag2;
if Cummulative New Tution (Calculated){i} < Actual Total Tution Target{i} then do;
Flag{i} = "Process" ;
end;
else do;
if first.course_rank then do;
Flag{i} = "CALC" ;
end;
else do;
Flag{i} = "STOP" ;
end;
end;
run;
What I am doing wrong ? why Flag{i} sets to "CALC" for rest of the rows. I am guessing somehow I need counter or way to move to "Next" Observation.
Please Help
Your BY statement
by student course course_rank;
means that whenever first.course is set, so is first.course_rank . As a result every record in your example has first.course_rank = 1.
Even if I use first.student, it will still create same results, so I am running out of ideas here.
Can you please show part of the input file ?
I believe I figured out solution with help of one of the expert.
Data test ;
set test;
by student;
retain temp1-temp2 ;
array flag{2} flag1 - flag2;
array temp2} temp1 - temp2;
if first.student then temp{i} = 0;
if Cummulative New Tution (Calculated){i} < Actual Total Tution Target{i} then do;
Flag{i} = "Process" ;
end;
else if temp{i} then do;
Flag{i} = "STOP";
end;
else do;
Flag{i} = "CALC";
temp{i} = 1;
end;
run;
That code will not run.
Dear Tinu,
Dkb is correct that your code didn't work. You can try this one, but you need make sure that my logic is correct. Pay attention to flag1 and flag2 in obs=3 vs obs=4, since my result is different with yours.
DATA have (drop=flag1 flag2);
input student course $ courserank tution Cumtution_cal Newtution Cum_New_tution Act_tui1 flag1 $ Act_tui2 flag2 $;
cards;
123 BIO 2 900 900 1000 1000 2500 Process 2800 Process
123 CHE 2 200 1100 300 1300 2500 Process 2800 Process
123 ENG 3 700 1800 800 2100 2500 Process 2800 Process
123 PHY 3 500 2300 500 2600 2500 CALC 2800 Process
123 MAT 4 300 2600 300 2900 2500 STOP 2800 CALC
123 BUS 4 400 3000 400 3300 2500 STOP 2800 STOP
123 COM 5 500 3500 1000 4300 2500 STOP 2800 STOP
;
run;
proc print;
run;
Data want ;
set have;
length flag1 $ 7. flag2 $ 7.;
flag1='n';
flag2='n';
if Cum_New_tution lt Act_tui1 and Cum_New_tution lt Act_tui2 then do;
flag1='Process';
flag2='Process';
output;
end;
else if Cum_New_tution gt Act_tui1 and Cum_New_tution lt Act_tui2 then do;
flag1='CALC';
flag2='Process';
output;
end;
else if Cum_New_tution lt Act_tui1 and Cum_New_tution gt Act_tui2 then do;
flag1='Process';
flag2='CALC';
output;
end;
else if Cum_New_tution gt Act_tui1 and Cum_New_tution gt Act_tui2 then do;
flag1='Stop';
flag2='Stop';
output;
end;
run;
proc print;
run;
Good luck!
Harry Liu
Dear Tinu,
Dkb is correct that the code you mentioned didn't work, especially the second if statement.
You can try this one, but you need keep in mind that my result is a little bit of different with yours. Please pay more attention to flag1 and flag2 on obs=3 and obs=4.
DATA have (drop=flag1 flag2);
input student course $ courserank tution Cumtution_cal Newtution Cum_New_tution Act_tui1 flag1 $ Act_tui2 flag2 $;
cards;
123 BIO 2 900 900 1000 1000 2500 Process 2800 Process
123 CHE 2 200 1100 300 1300 2500 Process 2800 Process
123 ENG 3 700 1800 800 2100 2500 Process 2800 Process
123 PHY 3 500 2300 500 2600 2500 CALC 2800 Process
123 MAT 4 300 2600 300 2900 2500 STOP 2800 CALC
123 BUS 4 400 3000 400 3300 2500 STOP 2800 STOP
123 COM 5 500 3500 1000 4300 2500 STOP 2800 STOP
;
run;
proc print;
run;
Data want ;
set have;
length flag1 $ 7. flag2 $ 7.;
flag1='n';
flag2='n';
if Cum_New_tution lt Act_tui1 and Cum_New_tution lt Act_tui2 then do;
flag1='Process';
flag2='Process';
output;
end;
else if Cum_New_tution gt Act_tui1 and Cum_New_tution lt Act_tui2 then do;
flag1='CALC';
flag2='Process';
output;
end;
else if Cum_New_tution lt Act_tui1 and Cum_New_tution gt Act_tui2 then do;
flag1='Process';
flag2='CALC';
output;
end;
else if Cum_New_tution gt Act_tui1 and Cum_New_tution gt Act_tui2 then do;
flag1='Stop';
flag2='Stop';
output;
end;
run;
proc print;
run;
Good luck!
Harry Liu
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.