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

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

turcay — Sun, 05 Jun 2016 13:53:36 GMT

Hello everyone,

I would like to ask my question to you. Again, it includes to get the data set output of some procedures. I know, SAS doesn’t let us to get the data set which has alredy had same structure with Report output but I need to create my desired output with some other methods.

I have a sample code as below(My real data is too big). Proc Tabulate almost can provide my desired output but I need some additional columns on my desired output.

Sample Data Set;

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;

Here is my desired output;

My first problem is to show values as zero even they don’t exist in the data set. For Example, in my sample data set there is no “1” values for "RankVariable" so “PROC TABULATE” doesn’t bring them in its report output, I also need this values as “0”.

Secondly, I need to add cumulative column but this column needs to show the values being percentage values.

Current Proc Tabulate Statement;

Proc Tabulate Data=Have Out=Have2;
Class  RankVariable / Order=Unformatted Missing;
Class Variable1 / Order=Unformatted Missing;
Table RankVariable , Variable1;
Run;

I also need this report output as data set. It shouldn’t be same structure, I need the following data set.

Data Set Desired;

Actually, this following steps could help me to create the data set but it is too manual, I think there should be more simple method to do the foreigoing demands. At this point, I would like to get your helps.

Proc Sql;
Create table ZeroCount AS
Select 
Count(Case When Variable1=0 And RankVariable=0 Then 0 Else . End) AS N0
,Count(Case When Variable1=0 And RankVariable=1 Then 1 Else . End) AS N1
,Count(Case When Variable1=0 And RankVariable=2 Then 2 Else . End) AS N2
From Have;
Create Table OneCount AS
Select
Count(Case When Variable1=1 And RankVariable=0 Then 0 Else . End) AS N0
,Count(Case When Variable1=1 And RankVariable=1 Then 1 Else . End) AS N1
,Count(Case When Variable1=1 And RankVariable=2 Then 2 Else . End) AS N2
From Have;
Quit;

PROC SQL;
CREATE TABLE CountTable AS 
SELECT * FROM ZEROCOUNT
OUTER UNION CORR 
SELECT * FROM ONECOUNT;
Quit;
 
 
PROC TRANSPOSE DATA=CountTable
     OUT=CountTable2
     PREFIX=Col
     NAME=Count;
     VAR N0 N1 N2;
RUN;
QUIT;
 
Data Cumulative;
Set CountTable2;
CumulativeColumn + Col2;
Run;

Thank you

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

Cynthia_sas — Sun, 05 Jun 2016 15:49:43 GMT

Hi:
Have you looked into the use of PRELOADFMT with PROC TABULATE? You will need a user-defined format, but then you can show all possible categories even when the category is not present in the data. There are examples in the doc for how to use PRELOADFMT.
cynthia

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

Cynthia_sas — Sun, 05 Jun 2016 15:58:35 GMT

Hi:
It seems like this question is related to this previous post: https://communities.sas.com/t5/Base-SAS-Programming/Get-Number-of-Zero-Values-and-Get-Percentage-of-Zero-Values/m-p/273941/highlight/true#M54600

was there something about that post that has changed since you marked it as solved?

cynthia

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

turcay — Sun, 05 Jun 2016 16:08:26 GMT

Hello Cynthia,

After you mentioned the PRELOADFMT in your previous post , I remembered and checked my previous question which you shared with me as above. Now, I'm checking that post to reach my aim.

Actually, because I used Reeza's code, I didn't investigate PRELOADFMT sample deeply. I'm looking your sample PRELOADFMT code then I will try to create my desired output report and data set by using PRELOADFMT.

Thank you,

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

Ksharp — Mon, 06 Jun 2016 02:43:36 GMT

It is more suitable for PROC REPORT.

PROC TABULATE don't have statistical like CUMPCT.

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 report data=have nowd completerows ;
columns RankVariable Variable1 CumulativePercent ;
define RankVariable/group format=fmt. preloadfmt;
define Variable1 /across;
define CumulativePercent /computed format=percent8.2;
compute before;
 cumpct=0;
 sum=sum(_c2_,_c3_);
endcomp;
compute CumulativePercent ;
 cumpct+sum(_c2_,_c3_)/sum;
 CumulativePercent=cumpct;
endcomp;
run;

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

turcay — Mon, 06 Jun 2016 07:26:38 GMT

Xia,

Thank you very much for providing foregoing code 🙂

But is %44.44 right? shouldn't be it %40.00 instead of %44.44? I'm not sure.

Thank you,

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

Ksharp — Mon, 06 Jun 2016 07:48:57 GMT

But you only have 9 obs , right ?

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

turcay — Mon, 06 Jun 2016 08:02:38 GMT

Xia,

For this sample data set, yes. But in my real data set, I have 80.000 obs and it seems to me it takes to much time, I'm still waiting.

Thank you,

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

Ksharp — Mon, 06 Jun 2016 08:11:40 GMT

This could get you a little faster .

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;

%let dsid=%sysfunc(open(have));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));

proc report data=have nowd completerows ;
columns RankVariable Variable1 CumulativePercent ;
define RankVariable/group format=fmt. preloadfmt;
define Variable1 /across;
define CumulativePercent /computed format=percent8.2;
compute CumulativePercent ;
 cumpct+sum(_c2_,_c3_)/&nobs;
 CumulativePercent=cumpct;
endcomp;
run;

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

turcay — Mon, 06 Jun 2016 09:24:39 GMT

Xia,

Thank you very much for your attention and providing this code.

But it seems that it takes too much time for 80.000 obs. I think for 80.000 obs, it can be better to do the calculation outside of PROC REPORT. What do you think?

Thank you

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

turcay — Mon, 06 Jun 2016 23:15:37 GMT

Cynthia,

Am I writing syntax incorrect?

proc format;
value fmt
 0="0"
 1="1"
 2="2";
run;
Proc Tabulate Data=Have Missing Out=Have2;
Class  RankVariable Variable1 / Preloadfmt ;
Table RankVariable , Variable1 / Printmiss;
Format RankVariable Variable1 $fmt.;
Run;

Thank you

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

Ksharp — Tue, 07 Jun 2016 01:04:34 GMT

Sure.

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 noprint;
select count(*) into : nobs from have;

create table temp as
select b.RankVariable,a.Variable1 
 from have as a full join level as b on
  a.RankVariable=b.RankVariable;
  
create table want as
select RankVariable,sum(Variable1=0) as Variable1_0 label='0',
                    sum(Variable1=1) as Variable1_1 label='1'
 from temp
  group by RankVariable;
quit;
data want;
 set want ;
 CumulativePercent+(Variable1_0+Variable1_1)/&nobs;
 format CumulativePercent percent8.2;
run;
proc report data=want nowd;
columns RankVariable ('Variable1' Variable1_0 Variable1_1) CumulativePercent ;
define RankVariable /display;
run;

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

turcay — Tue, 07 Jun 2016 11:32:47 GMT

Thank you very much Xia,

Your sample code helped me so much, however I still didn't reach my desired output.

Let's say that my RankVariable has 10 values, to create my desired output, I modified your sample data as below . It is almost done. In the percentage column, I need to add one additional thing to reach my goal.

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;
 
Proc Sql Noprint;
Select Count(*) Into : Nobs From Have;
 
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;
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;

Data Want3;
Set Want2 ;
CumulativePercent+Variable1_1/&Nobs;
Format CumulativePercent Percent8.2;
Run;
 
 
Proc Report Data=Want2 Nowd;
Columns RankVariable  ('Variable1_1' Variable1_0 Variable1_1) CumulativePercent ;
Define RankVariable  / Display;
Run;

My current report output,

Desired Report;

PS: I need to get cumulative percentage values just for NPL_F_1, it is "Variable1_1" in my sample data set.

Can somebody still help me?

Thank you

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

Cynthia_sas — Tue, 07 Jun 2016 13:30:37 GMT

Hi:
Since you are creating the Cumulative Percent yourself, you have 2 choices: multiply by 100 in the DATA step where you calculate Cumulative Percent OR do it in PROC REPORT. What is your DEFINE statement for the CumulativePercentage variable. The SAS PERCENT format does an automatic multiply by 100, why not try that? Or, just multiply by 100 in the DATA step if you don't want a Percent sign.

cynthia

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

turcay — Tue, 07 Jun 2016 13:58:25 GMT

Thank you Cynthia,

So I have two choices,

I can do it in Data step or I can do it in Proc Report.

Actually, Xia prepared codes for both Data Step and Proc Report statements.

Because I have 80.000 obs in my real Data Set, it takes to much time when we use Proc Report statement. So I decided to create in Data Set.

Xia's code seems correct, but there are some diferences between Excel and SAS output. I think the following step needs to change. I multiplied with 100, but it didn't seem correct. I need to make some changes on Cumulative percentage step.

Data Want3;
Set Want2 ;
CumulativePercent+Variable1_1/&Nobs;
Format CumulativePercent Percent8.2;
Run;

I also tried to use PRELOADFMT. in my one of the previos post, but I got an error.

I'm sorry, I'm little bit confused 😞 I'm going to make some search more detailed. Let me check again.

Thank you

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

turcay — Tue, 07 Jun 2016 14:04:29 GMT

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,

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

turcay — Tue, 07 Jun 2016 16:38:50 GMT

@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

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

turcay — Tue, 07 Jun 2016 22:40:22 GMT

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

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

Ksharp — Wed, 08 Jun 2016 01:56:02 GMT

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;

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

turcay — Wed, 08 Jun 2016 10:22:31 GMT

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,