How to - Get Desired Data Set Output & Report Output By Using Data Step or Proc Sql

[turcay]

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,

[turcay]

@Ksharp,

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

[turcay]

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

[Ksharp]

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;

[turcay]

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,

[Ksharp · Accepted solution]

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;