topic Re: creating a table consisting of 2 category in row and column and mean of continous variable in mi in SAS Programming

creating a table consisting of 2 category in row and column and mean of continous variable in middle

harsh0404 — Tue, 03 Sep 2019 20:28:09 GMT

So I have 10 million rows in a dataset and this is what data looks like (on left). consists of decile ,a category and profits.

Now I need to create something on my right. Decile as rows, category as column and mean of profits as the values in the table. So for example - all the values that are in decile 1 and category a, their mean profit is 300 dollars.

Re: creating a table consisting of 2 category in row and column and mean of continous variable in mi

hashman — Tue, 03 Sep 2019 20:59:15 GMT

@harsh0404:

Methinks this is what proc TRANSPOSE is designed for:

data have ;                                        
  input decile category :$1.profit ;               
  cards ;                                          
1  a  300                                          
2  h  500                                          
4  t  670                                          
6  u  540                                          
8  q  109                                          
1  q  409                                          
9  r  870                                          
;                                                  
run ;                                              
                                                   
proc sort data = have out = haves ;                
  by decile ;                                      
run ;                                              
                                                   
proc transpose data = haves out = want (drop = _:) ;
  by decile ;                                      
  id category ;                                    
  var profit ;                                     
run ;

Kind regards

Paul D.

Re: creating a table consisting of 2 category in row and column and mean of continous variable in mi

ballardw — Tue, 03 Sep 2019 23:07:20 GMT

Or possibly as simple as:

proc tabulate data=have;
   class decile category;
   var profit;
   table decile=' ', 
         category*profit=''*mean=''
         /misstext=' ' box=decile
   ;
run;

Re: creating a table consisting of 2 category in row and column and mean of continous variable in mi

hashman — Tue, 03 Sep 2019 23:46:12 GMT

@ballardw:

Had thought of, too (love proc TAB). If the OP needs a report rather than a data set, it's picture perfect.