Help using Base SAS procedures

Accessing - "Next" Observation for manipulation

Reply
New Contributor
Posts: 3

Accessing - "Next" Observation for manipulation

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

Contributor dkb
Contributor
Posts: 53

Re: Accessing - "Next" Observation for manipulation

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.

New Contributor
Posts: 3

Re: Accessing - "Next" Observation for manipulation

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

Frequent Contributor
Posts: 95

Re: Accessing - "Next" Observation for manipulation

Can you please show part of the input file ?

New Contributor
Posts: 3

Re: Accessing - "Next" Observation for manipulation

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;

Contributor dkb
Contributor
Posts: 53

Re: Accessing - "Next" Observation for manipulation

That code will not run.

Contributor
Posts: 23

Re: Accessing - "Next" Observation for manipulation

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

Contributor
Posts: 23

Re: Accessing - "Next" Observation for manipulation

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

Ask a Question
Discussion stats
  • 7 replies
  • 513 views
  • 0 likes
  • 4 in conversation