DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 395
Accepted Solution

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

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.


Accepted Solutions
Solution
‎11-15-2015 09:51 AM
Super Contributor
Posts: 395

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

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;

 

View solution in original post


All Replies
Super User
Posts: 5,438

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

Having some problem following you.

Attach sample input (FREQ output) and wanted outcome.

Data never sleeps
Super Contributor
Posts: 395

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

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);

Cumulative.png

Solution
‎11-15-2015 09:51 AM
Super Contributor
Posts: 395

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

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;

 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 204 views
  • 1 like
  • 2 in conversation