topic Sorting The Descending Way Cumulatively and Adding and Substracting Two Successive Rows in SAS Programming

Sorting The Descending Way Cumulatively and Adding and Substracting Two Successive Rows

turcay — Thu, 15 Oct 2015 16:01:21 GMT

Hello everyone,

I have listed Frequency_Good , Frequency_Bad, Frequency_Density columns cumulatively as you can see on the code below. How can i list these three columns in the opposite order without effectint the other columns.

Also, I would like to add the GoodCase column with the GoodCase next row and remove BadCase column from the BadCase next row. How can i create these two columns into a new column?

data Sample;
length ND 8 D 8;
infile datalines missover dlm=",";
input ND D;
datalines;
21165,58
6307,23
6943,35
11653,57
17285,163
20045,236
20094,343
21978,393
23882,554
21647,576
16383,450
12641,357
7844,256
4530,163
2468,110
1105,44
448,29
370,34
300,33
250,28
200,27
150,15
;
data Sample2;
	set Sample;
Total=ND+D;
run;
Proc sql;
Create table Sample3 as
select ND,D,Total,Sum(ND) AS TotalND,Sum(D) as TotalD, Sum(Total) AS TotTotal
from Sample2;
quit;
data Sample4(drop=TotalD TotalND TotTotal);
 set sample3;
 Rating_Class=Total/TotTotal;
 IF D=0 then DefaultRate=0;
 else DefaultRate=D/Total;
 Good_Case=ND/TotalND;
 Bad_Case=D/TotalD;
 Density_Function=Total/TotTotal;
Frequency_Good+Good_Case;
Frequency_Bad+Bad_Case;
Frequency_Density+Density_Function;
 run;

Thank you.

Re: Sorting The Descending Way Cumulatively and Adding and Substracting Two Successive Rows

LinusH — Thu, 15 Oct 2015 19:42:00 GMT

Having some problem following you.

Attach sample input (FREQ output) and wanted outcome.

Re: Sorting The Descending Way Cumulatively and Adding and Substracting Two Successive Rows

turcay — Fri, 16 Oct 2015 13:57:59 GMT

I solved the 'Adding and Substracting Two Successive Rows' question like as below. But i still didn't create the column as cumulative. I added the data set image below. I created the columns as cumulative but I need to sort opposite of the last three columns.

good_case_lag=lag(good_case);
bad_case_lag=lag(bad_case);
good_case2=sum(good_case,good_case_lag);
bad_case2=sum(bad_case_lag,-1*bad_case);

Re: Sorting The Descending Way Cumulatively and Adding and Substracting Two Successive Rows

turcay — Thu, 12 Nov 2015 08:02:57 GMT

Hello @LinusH,

Firstly, i added obsnum column by using _n_ option and sort the column by descending after that i create three cumulative columns. Please check the code if you agree i will accept the answer as solved.

Thank you @LinusH

data dataset;
set dataset2;
obsnum=_n_; 
...
...
...
run;

proc sort data=dataset2 out=dataset3(keep=obsnum Column1);
by descending obsnum;
run;

data dataset3;
set dataset3;
CumColumn1+Column1;
run;

proc sort data=dataset3;
by obsnum;
run;