BookmarkSubscribeRSS Feed
Tinu
Calcite | Level 5

How do  I manipulate value of "next" observation in Column Cumulative New Tuition ( ? ?)

StudentCourseCourse Rank
TutionCumulative Tuition (Calculated)New TutionCummulative New Tution (Calculated)Actual Total Tution Target - 1Flag1Actual Total Tution Target - 2
Flag2
123BIO2900900100010002500Process2800Process
123CHE2200110030013002500Process2800Process
123ENG3700180080021002500Process2800Process
123PHY3500230050026002500CALC2800Process
123MAT4300260030029002500STOP2800CALC
123BUS4400300040033002500STOP2800STOP
123COM55003500100043002500STOP2800STOP

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

7 REPLIES 7
dkb
Quartz | Level 8 dkb
Quartz | Level 8

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.

Tinu
Calcite | Level 5

Even if I use first.student, it will still create same results, so I am running out of ideas here.

Jim_G
Pyrite | Level 9

Can you please show part of the input file ?

Tinu
Calcite | Level 5

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;

dkb
Quartz | Level 8 dkb
Quartz | Level 8

That code will not run.

HarryLiu
Obsidian | Level 7

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

HarryLiu
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1157 views
  • 0 likes
  • 4 in conversation