topic Re: Accessing - "Next" Observation for manipulation in SAS Procedures

Accessing - "Next" Observation for manipulation

Tinu — Wed, 08 Jul 2015 23:21:57 GMT

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;

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

Re: Accessing - "Next" Observation for manipulation

dkb — Thu, 09 Jul 2015 02:40:40 GMT

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.

Re: Accessing - "Next" Observation for manipulation

Tinu — Thu, 09 Jul 2015 14:41:48 GMT

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

Re: Accessing - "Next" Observation for manipulation

Jim_G — Thu, 09 Jul 2015 15:31:06 GMT

Can you please show part of the input file ?

Re: Accessing - "Next" Observation for manipulation

Tinu — Thu, 09 Jul 2015 16:59:15 GMT

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;

Re: Accessing - "Next" Observation for manipulation

dkb — Thu, 09 Jul 2015 17:18:25 GMT

That code will not run.

Re: Accessing - "Next" Observation for manipulation

HarryLiu — Thu, 09 Jul 2015 21:58:56 GMT

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

Re: Accessing - "Next" Observation for manipulation

HarryLiu — Fri, 10 Jul 2015 03:03:33 GMT

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