BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
turcay
Lapis Lazuli | Level 10

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

3 REPLIES 3
LinusH
Tourmaline | Level 20

Having some problem following you.

Attach sample input (FREQ output) and wanted outcome.

Data never sleeps
turcay
Lapis Lazuli | Level 10

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

turcay
Lapis Lazuli | Level 10

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;

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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