Thanks Jay for your help...very helpful. ... View more

Some more questions. None of your example data has any value for LEVEL. So what actual role does this play in the process? If it has one you need to include data that shows it. Otherwise I would say that your need for using LEVEL in the BY process is not needed.   For the example you show you also do not want TERM in the by process though the data likely needs to be in the correct order. For the example data this seems to do what is requested. Note that I added "newintake" as the calculated value and left in your example data set intake. data want; set have ; by StudentUID Programs AcademicYearBeg2; length newintake $ 10.; comp= lag(AcademicYearBeg2); if first.Programs then newintake='Initial'; else if AcademicYearBeg2=comp then newintake='Initial'; else newintake='Continued'; drop comp; run; Since you comparison is based on the AcademicYearBeg2 using the Lag function to get the previous for comparison with the current. ... View more

Hi Jag....that worked perfectly.....one more thing...can the second y axis be used as a line on the graph which will show to the overall percentage. I am trying to compare the departments with bars and would like to have a line to show the overall percentage if that is possible. Thanks. ... View more

Hi Shmuel....thanks for your help and suggestion.....I wasn't able to get the suggestion of creating max_iter in the data have but ended of creating a dataset of the number of rows for each ID and then taking the maximum number from all ID's and then assigned it to a macro variable. I also ended up defining all of the lag variable first before calling them. Nevertheless, it seems to work.....thanks once again. ... View more

Yes...I tried a larger and smaller value and nothing would change. ... View more

DBMS=XLSX does not have any option to force a type on a column.  The type is determined by the type of the cells in the column. ... View more

Hi Tom....I was able to get the required table. It may not be the most efficient way to get the results. Thanks   data have; format ID Counts Counts1 ShowAmount NumberDays NumberDaysMonth NumberDaysBeg NumberDaysEnd BegAmount best12.; infile datalines4 dlm='7F'x missover dsd; input ID Counts Counts1 ShowAmount NumberDays NumberDaysMonth NumberDaysBeg NumberDaysEnd BegAmount : best32.; datalines4; 152 1 1 . 36 28 0 28 3650 152 1 2 . 36 8 0 8 3650 152 2 3 . 85 14 0 14 4100 152 2 4 . 85 31 0 31 4100 152 2 5 700 85 30 10 20 4100 152 2 6 . 85 10 0 10 4100 152 3 7 . 71 19 0 19 4650 152 3 8 450 71 29 28 1 4650 152 3 9 . 71 23 0 23 4650 152 4 10 .85 25 0 25 4650 152 4 11 .85 36 0 31 4650 152 4 12 .85 29 0 29 4650 ;;;; data have; set have; if ShowAmount = . then ShowAmount = 0; run; data have(drop=NumberDaysMonth2 NumberDays2); set have; by ID Counts Counts1 notsorted; NumberDaysMonth2 = lag(NumberDaysMonth); NumberDays2 = lag(NumberDays1); NumberDays1 = NumberDays2 - NumberDaysMonth2; if Counts ^=lag(Counts) then NumberDays1 = NumberDays; run; data want; set have; by ID Counts Counts1 notsorted; if first.ID and first.Counts and first.Counts1 then do; BegAmount1 = BegAmount; EndAmount = (BegAmount + ShowAmount); NumberDays1 = NumberDays1; MonthlyAmount = (((NumberDaysBeg/NumberDays1)*BegAmount1) + ((NumberDaysEnd/NumberDays1)*EndAmount)); EndAmount1 = (EndAmount - MonthlyAmount); end; run; data want1; set want; if not first.Counts1 then EndAmount2 = lag(EndAmount1); else EndAmount2 = EndAmount2; if not first.Counts1 then BegAmount2 = lag(BegAmount); else BegAmount2 = BegAmount2; if not first.Counts1 then NumberDays2 = lag(NumberDays1); else NumberDays2 = NumberDays2; if not first.Counts1 then NumberDaysMonth2 = lag(NumberDaysMonth); else NumberDaysMonth2 = NumberDaysMonth2; if not first.Counts1 then Counts2 = lag(Counts); else Counts2 = Counts; if missing(BegAmount1) then BegAmount1 = BegAmount - BegAmount2 + EndAmount2; else BegAmount1 = BegAmount1; if missing(EndAmount) then EndAmount = BegAmount - BegAmount2 + ShowAmount + EndAmount2; else EndAmount = EndAmount; if missing(NumberDays1) and Counts = Counts2 then NumberDays1 = NumberDays2 - NumberDaysMonth2; else NumberDays1 = NumberDays1; if missing( MonthlyAmount) then MonthlyAmount = (((NumberDaysBeg/NumberDays1)*BegAmount1) + ((NumberDaysEnd/NumberDays1)*EndAmount)); else MonthlyAmount = MonthlyAmount; if missing(EndAmount1) then EndAmount1 = (EndAmount - MonthlyAmount); else EndAmount1 = EndAmount1; output; run; %macro test(likevar); %do i=1 %to 10; data want1; set want1; if not first.Counts1 then EndAmount2 = lag(EndAmount1); else EndAmount2 = EndAmount2; if not first.Counts1 then BegAmount2 = lag(BegAmount); else BegAmount2 = BegAmount2; if not first.Counts1 then NumberDays2 = lag(NumberDays1); else NumberDays2 = NumberDays2; if not first.Counts1 then NumberDaysMonth2 = lag(NumberDaysMonth); else NumberDaysMonth2 = NumberDaysMonth2; if not first.Counts1 then Counts2 = lag(Counts); else Counts2 = Counts2; if missing(BegAmount1) then BegAmount1 = BegAmount - BegAmount2 + EndAmount2; else BegAmount1 = BegAmount1; if missing(EndAmount) then EndAmount = BegAmount - BegAmount2 + ShowAmount + EndAmount2; else EndAmount = EndAmount; if missing(NumberDays1) and Counts = Counts2 then NumberDays1 = NumberDays2 - NumberDaysMonth2; else NumberDays1 = NumberDays1; if missing( MonthlyAmount) then MonthlyAmount = (((NumberDaysBeg/NumberDays1)*BegAmount1) + ((NumberDaysEnd/NumberDays1)*EndAmount)); else MonthlyAmount = MonthlyAmount; if missing(EndAmount1) then EndAmount1 = (EndAmount - MonthlyAmount); else EndAmount1 = EndAmount1; output; run; %end; %mend test; %test ... View more

Changed thread title from "Runnung Total" to "Running Total" ... View more

Hi..Mkeintz….Yes you are right. That is what I was wanting...thanks for your help... ... View more

Thanks DanH ... View more

Hi Reeza…..No I haven't tried applying variable labels. I think I will have to just edit the table as needed. But thank you for your help...greatly appreciated. ... View more

Thanks Paige....that worked..... ... View more