Actually, what I try to say that, in my foregoing Desired and Current output, in both image NPL_F_1 values same, why CumuluativePercent comes as different. I just didn't understand.
Thank you,
The following code creates my desired output but I made so much Data Step, shouldn't be different method to get same result?
/*For Ranking*/
Data Level;
RankVariable =0;Output;
RankVariable =1;Output;
RankVariable =2;Output;
RankVariable =3;Output;
RankVariable =4;Output;
RankVariable =5;Output;
RankVariable =6;Output;
RankVariable =7;Output;
RankVariable =8;Output;
RankVariable =9;Output;
RankVariable =10;Output;
Run;
/*Ranking*/
Proc Sql ;
Create Table Want As
Select B.RankVariable
,Sum(Variable1=0) As Variable1_0 Label='0'
,Sum(Variable1=1) As Variable1_1 Label='1'
From Have As A
Full Join Level As B
On A.RankVariable =B.RankVariable
Group By B.RankVariable ;
Quit;
/*Lagging for proper data set*/
Data Want2;
Set Want;
Variable1_0=Lag(Variable1_0);
Variable1_1=Lag(Variable1_1);
If Variable1_0=. Then Variable1_0=0;
If Variable1_1=. Then Variable1_1=0;
Run;
/*Creating Cumulative Column*/
Data Want3;
Set Want2 ;
Cumulative+Variable1_1;
Run;
/*Get Total Value*/
PROC SQL;
Create Table Want4 As
Select *,Sum(Variable1_1) AS Total
From Want3 ;
QUIT;
/*Cumulative column divide Total column desired result*/
Data Want5;
Set Want4;
CumulativePercent=(Cumulative/Total);
Format CumulativePercent Percent8.2;
Run;
/*Reporting*/
Proc Report Data=Want5 Nowd;
Columns Rank_P_NPL_F1 ('NPL_F_1' Variable1_0 Variable1_1) CumulativePercent ;
Define Rank_P_NPL_F1 / Display;
Run;
Thank you
The following code is the best which I could do it. It provides my desired output but I'm not sure whether this method is correct or not.
Also I'm still can't get it the PRELOADFMT, even though, I put the statement in my Define option, I cannot see the zero values 😞
/*PROC RANK DATA = BeforeHave DESCENDING GROUPS=3 TIES=MEAN OUT=Have;
VAR Variable2;
RANKS RankVariable ;
RUN;
QUIT;*/
Data Have;
Length Variable1 8 RankVariable 8;
Infile Datalines Missover;
Input Variable1 RankVariable;
Datalines;
0 0
1 2
0 0
1 0
0 2
1 0
1 2
1 2
0 2
;
Run;
Proc Sql ;
Create Table Want As
Select Data1.*, Sum(Data1.Variable1_1) As Total From
(Select RankVariable
,Sum(Variable1=0) As Variable1_0 Label='0'
,Sum(Variable1=1) As Variable1_1 Label='1'
From Have
Group By RankVariable) AS Data1;
Quit;
Data Want2;
Set Want;
Lag_Variable1_0=Lag(Variable1_0);
Lag_Variable1_1=Lag(Variable1_1);
If Lag_Variable1_0=. Then Lag_Variable1_0=0;
If Lag_Variable1_1=. Then Lag_Variable1_1=0;
Cumulative+Variable1_1;
CumulativePercent=(Cumulative/Total);
Run;
proc format;
value fmt
0='0'
1='1'
2='2';
run;
Proc Report Data=Want2 Nowd;
Columns RankVariable ('NPL_F_1' Lag_Variable1_0 Lag_Variable1_1) CumulativePercent ;
Define RankVariable/ Group Format=Fmt. Preloadfmt;
Run;
Thank you
Yes. You are right .
Just Change
CumulativePercent+(Variable1_0+Variable1_1)/&nobs;
into
CumulativePercent+ Variable1_1/&nobs;
AND
Change SQL
select count(*) into : nobs from have
into
select count(*) into : nobs from have
where Variable1=1;
OR PROC TABULATE+PROC REPORT
Data Have;
Length Variable1 8 RankVariable 8;
Infile Datalines Missover;
Input Variable1 RankVariable;
Datalines;
0 0
1 2
0 0
1 0
0 2
1 0
1 2
1 2
0 2
;
Run;
proc format;
value fmt
0="0"
1="1"
2="2";
run;
Proc Tabulate Data=Have Out=Have2 ;
Class RankVariable / Preloadfmt ;
Class Variable1;
Table RankVariable,Variable1 / Printmiss misstext='0';
Format RankVariable fmt.;
Run;
proc sql noprint;
select count(*) into : nobs from have
where Variable1=1;
quit;
options missing='0';
proc report data=have2 nowd ;
columns RankVariable n,Variable1 cum_pct;
define RankVariable/group;
define Variable1/across;
define n/analysis ' ';
define cum_pct/computed format=percent8.2;
compute cum_pct;
temp+_c3_/&nobs ;
cum_pct+temp;
endcomp;
run;
Hello @Ksharp,
After I did the changes it worked successfully. Thank you very much.
For your PROC REPORT code, it also worked fine but as I said before, it takes too much time for 80.000 observations.
I also want to ask one more question, in my following code, how can I bring the "0" values in my output?_
Proc Sql ;
Create Table Want As
Select Data1.*, Sum(Data1.Variable1_1) As Total From
(Select RankVariable
,Sum(Variable1=0) As Variable1_0 Label='0'
,Sum(Variable1=1) As Variable1_1 Label='1'
From Have
Group By RankVariable) AS Data1;
Quit;
Data Want2;
Set Want;
Lag_Variable1_0=Lag(Variable1_0);
Lag_Variable1_1=Lag(Variable1_1);
If Lag_Variable1_0=. Then Lag_Variable1_0=0;
If Lag_Variable1_1=. Then Lag_Variable1_1=0;
Cumulative+Variable1_1;
CumulativePercent=(Cumulative/Total);
Run;
Thank you,
Here is : Data Have; Length Variable1 8 RankVariable 8; Infile Datalines Missover; Input Variable1 RankVariable; Datalines; 0 0 1 2 0 0 1 0 0 2 1 0 1 2 1 2 0 2 ; Run; Data Level; RankVariable =0;Output; RankVariable =1;Output; RankVariable =2;Output; Run; Proc Sql ; Create Table Want As Select Data1.*, Sum(Data1.Variable1_1) As Total From ( select b.RankVariable,coalesce(a.Variable1_0,0) as Variable1_0 ,coalesce(a.Variable1_1,0) as Variable1_1 from (Select RankVariable ,Sum(Variable1=0) As Variable1_0 Label='0' ,Sum(Variable1=1) As Variable1_1 Label='1' From Have Group By RankVariable) as a right join level as b on a.RankVariable =b.RankVariable ) AS Data1; Quit; Data Want2; Set Want; Lag_Variable1_0=Lag(Variable1_0); Lag_Variable1_1=Lag(Variable1_1); If Lag_Variable1_0=. Then Lag_Variable1_0=0; If Lag_Variable1_1=. Then Lag_Variable1_1=0; Cumulative+Variable1_1; CumulativePercent=(Cumulative/Total); Run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.