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,

 

As you know, PROC TABULATE does not bring the values, if the range doesn't include values . For example, in my following sample. there is no value 0.0-0.2 range for this sample.However, it could have values for 0.0-0.2 range. I mean that for this sample, it doesn't include value for the range 0.0-0.2 but for another data set it can includes values.

 

So I want to show the range without considering whether the range includes values or not.

 

Here is my sample code

 

Data Have;
Length Target 8 Predicted 8 Bucket $ 30;
Infile Datalines Missover;
Input Target Predicted Bucket;
Datalines;
0 0.1 0.2-0.4
0 0.3 0.2-0.4
0 0.7 0.4-0.6
0 0.5 0.6-0.8
0 0.9 0.8-1.0
1 0.1 0.2-0.4
1 0.3 0.2-0.4
1 0.7 0.4-0.6
1 0.5 0.6-0.8
1 0.9 0.8-1.0
0 0.1 0.2-0.4
0 0.3 0.2-0.4
0 0.7 0.4-0.6
0 0.5 0.6-0.8
0 0.9 0.8-1.0
1 0.1 0.2-0.4
1 0.3 0.2-0.4
1 0.7 0.4-0.6
1 0.5 0.6-0.8
1 0.9 0.8-1.0
;
Run;

Proc Tabulate Data=Have;                                                               
   Var Predicted;                                                                                                                
   Class Bucket;                                        
   Class Target;          
   Classlev Target; 
   Classlev Bucket;                         
   Table Bucket , Predicted*Mean  Target*(N ColPctN) N ColPctN;                                              
                                                                                                              
Run;       

Here is the output of my sample

 

Sample.png

 

Here is my desired output

 

Desired.png

 

How can I build this structure? Can somebody help me, please?

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

You need MISSING option.

 

Data Have;
Length Target 8 Predicted 8 Bucket $ 30;
Infile Datalines Missover;
Input Target Predicted Bucket;
Datalines;
0 0.1 0.0-0.2
0 0.3 0.0-0.2
0 0.7 0.4-0.6
0 0.5 0.0-0.2
0 0.9 0.8-1.0
1 0.1 0.0-0.2
1 0.3 0.0-0.2
1 0.7 0.4-0.6
1 0.5 0.0-0.2
1 0.9 0.8-1.0
0 0.1 0.0-0.2
0 0.3 0.0-0.2
0 0.7 0.4-0.6
0 0.5 0.0-0.2
0 0.9 0.8-1.0
1 0.1 0.0-0.2
1 0.3 0.0-0.2
1 0.7 0.4-0.6
1 0.5 0.0-0.2
1 0.9 0.8-1.0
;
Run;

PROC FORMAT;
Value $Bucket 0.0-0.2="0.0-0.2"
			 0.2-0.4="0.2-0.4"
			 0.4-0.6="0.4-0.6"
			 0.6-0.8="0.6-0.8"
			 0.8-1.0="0.8-1.0";
Run;
Proc Tabulate Data=Have;                                                               
   Var Predicted;                                                                                                                
   Class Bucket / PreLoadFmt missing;                                        
   Class Target;          
   ClassLev Target; 
   ClassLev Bucket;  
   Format Bucket $Bucket.;
   Table Bucket , Predicted*Mean  Target*(N ColPctN) N ColPctN  /Printmiss MissText='"-"';                                                                                                                                                          
Run; 

View solution in original post

13 REPLIES 13
Ksharp
Super User

Use CLASSDATA=

 

Data Have;
Length Target 8 Predicted 8 Bucket $ 30;
Infile Datalines Missover;
Input Target Predicted Bucket;
Datalines;
0 0.1 0.2-0.4
0 0.3 0.2-0.4
0 0.7 0.4-0.6
0 0.5 0.6-0.8
0 0.9 0.8-1.0
1 0.1 0.2-0.4
1 0.3 0.2-0.4
1 0.7 0.4-0.6
1 0.5 0.6-0.8
1 0.9 0.8-1.0
0 0.1 0.2-0.4
0 0.3 0.2-0.4
0 0.7 0.4-0.6
0 0.5 0.6-0.8
0 0.9 0.8-1.0
1 0.1 0.2-0.4
1 0.3 0.2-0.4
1 0.7 0.4-0.6
1 0.5 0.6-0.8
1 0.9 0.8-1.0
;
Run;
proc sql;
create table classdata as
select * from
 (select distinct Bucket from have union select '0.0-0.2' from have(obs=1)),
 (select distinct Predicted from have),
 (select distinct Target from have);
quit;
Proc Tabulate Data=Have classdata=classdata;                                                               
   Var Predicted;                                                                                                                
   Class Bucket;                                        
   Class Target;          
   Classlev Target; 
   Classlev Bucket;                         
   Table Bucket , Predicted*Mean  Target*(N ColPctN) N ColPctN/misstext='"-"';                                              
                                                                                                              
Run;       

x.png

turcay
Lapis Lazuli | Level 10

@Ksharp,

 

Thank you very much for trying to help me.

 

But instead of "0.0-0.2" what the range of "0.2-0.4" was empty, For example, If I change my sample as below, so I need to change your code as below too.

 

Instead of this method, Can PreLoadFmt format is more efficent than this?

 

 

Data Have;
Length Target 8 Predicted 8 Bucket $ 30;
Infile Datalines Missover;
Input Target Predicted Bucket;
Datalines;
0 0.1 0.0-0.2
0 0.3 0.0-0.2
0 0.7 0.4-0.6
0 0.5 0.0-0.2
0 0.9 0.8-1.0
1 0.1 0.0-0.2
1 0.3 0.0-0.2
1 0.7 0.4-0.6
1 0.5 0.0-0.2
1 0.9 0.8-1.0
0 0.1 0.0-0.2
0 0.3 0.0-0.2
0 0.7 0.4-0.6
0 0.5 0.0-0.2
0 0.9 0.8-1.0
1 0.1 0.0-0.2
1 0.3 0.0-0.2
1 0.7 0.4-0.6
1 0.5 0.0-0.2
1 0.9 0.8-1.0
;
Run;
Proc sql;
create table classdata as
select * from
 (select distinct Bucket from have 
union select '0.0-0.2' from have(obs=1) 
union select '0.2-0.4' from have(obs=1)
union select '0.4-0.6' from have(obs=1)
union select '0.6-0.8' from have(obs=1)
union select '0.8-1.0' from have(obs=1)),
 (select distinct Predicted from have),
 (select distinct Target from have);
Quit;

Proc Tabulate Data=Have classdata=classdata;                                                               
   Var Predicted;                                                                                                                
   Class Bucket;                                        
   Class Target;          
   Classlev Target; 
   Classlev Bucket;                         
   Table Bucket , Predicted*Mean  Target*(N ColPctN) N ColPctN / misstext='"-"';                                                                                                                                                         
Run;       
Reeza
Super User

Create a format and use PRELOADFMT option. 

If you search on here or lexjansen.com you'll find many examples of the implementation. 

turcay
Lapis Lazuli | Level 10

@Reeza,

 

Thank you very much for trying to help me.

 

I tried to benefit from following link, but I couldn't succeed, it seems little bit complicated, can you help me to resolve this issue, please?

 

http://support.sas.com/resources/papers/proceedings11/239-2011.pdf 

 

PROC FORMAT;
Value Bucket 0.0-0.2="0.0-0.2"
			 0.2-0.4="0.2-0.4"
			 0.4-0.6="0.4-0.6"
			 0.6-0.8="0.6-0.8"
			 0.8-1.0="0.8-1.0";
Run;
Proc Tabulate Data=Have;                                                               
   Var Predicted;                                                                                                                
   Class Bucket / PreLoadFmt;                                        
   Class Target;          
   ClassLev Target; 
   ClassLev Bucket;  
/*   Format Bucket.;*/
   Table Bucket , Predicted*Mean  Target*(N ColPctN) N ColPctN  /Printmiss;                                                                                                                                                          
Run;       

Thank you

Reeza
Super User

You need the format applied to bucket variable. It looks like you started but didn't finish that line. 

 

This is also assumes bucket is a continuous variable. 

turcay
Lapis Lazuli | Level 10

@Reeza,Sometimes like this?

 

PROC FORMAT;
Value $Bucket 0.0-0.2="0.0-0.2"
			 0.2-0.4="0.2-0.4"
			 0.4-0.6="0.4-0.6"
			 0.6-0.8="0.6-0.8"
			 0.8-1.0="0.8-1.0";
Run;
Proc Tabulate Data=Have;                                                               
   Var Predicted;                                                                                                                
   Class Bucket / PreLoadFmt;                                        
   Class Target;          
   ClassLev Target; 
   ClassLev Bucket;  
   Format Bucket $Bucket.;
   Table Bucket , Predicted*Mean  Target*(N ColPctN) N ColPctN  /Printmiss MissText='"-"';                                                                                                                                                          
Run;       
Reeza
Super User

1. Are there any errors in your log? Your format doesn't look correct

2. Does it do what you want?

turcay
Lapis Lazuli | Level 10

@Reeza,

 

1-There aren't any error in my log. How can I fix my format?

2-Yes, it does.

 

Can you tell me that how can I write my format correct?

Reeza
Super User

Bucket is a character variable. The values you've defined for the format are numeric. 

Ksharp
Super User

You need MISSING option.

 

Data Have;
Length Target 8 Predicted 8 Bucket $ 30;
Infile Datalines Missover;
Input Target Predicted Bucket;
Datalines;
0 0.1 0.0-0.2
0 0.3 0.0-0.2
0 0.7 0.4-0.6
0 0.5 0.0-0.2
0 0.9 0.8-1.0
1 0.1 0.0-0.2
1 0.3 0.0-0.2
1 0.7 0.4-0.6
1 0.5 0.0-0.2
1 0.9 0.8-1.0
0 0.1 0.0-0.2
0 0.3 0.0-0.2
0 0.7 0.4-0.6
0 0.5 0.0-0.2
0 0.9 0.8-1.0
1 0.1 0.0-0.2
1 0.3 0.0-0.2
1 0.7 0.4-0.6
1 0.5 0.0-0.2
1 0.9 0.8-1.0
;
Run;

PROC FORMAT;
Value $Bucket 0.0-0.2="0.0-0.2"
			 0.2-0.4="0.2-0.4"
			 0.4-0.6="0.4-0.6"
			 0.6-0.8="0.6-0.8"
			 0.8-1.0="0.8-1.0";
Run;
Proc Tabulate Data=Have;                                                               
   Var Predicted;                                                                                                                
   Class Bucket / PreLoadFmt missing;                                        
   Class Target;          
   ClassLev Target; 
   ClassLev Bucket;  
   Format Bucket $Bucket.;
   Table Bucket , Predicted*Mean  Target*(N ColPctN) N ColPctN  /Printmiss MissText='"-"';                                                                                                                                                          
Run; 
turcay
Lapis Lazuli | Level 10

Thank you very much both @Ksharp and @Reeza 🙂

Ksharp
Super User
I think MISSING option is surplus . PRINTMISS is good enough .
turcay
Lapis Lazuli | Level 10

Okay, I will consider it Xia, thank you 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 3949 views
  • 4 likes
  • 3 in conversation