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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.