/**********************************************************************/


   /* ------------------------------------------------*/
   /* Example of a user-written format to reduce data */
   /* redundancy. A one-character variable represents */
   /* up to forty characters of information.          */
   /* ------------------------------------------------*/
data life;                                              
   input kingdom $ 1 species  $ 3-17;                   
   cards;                                               
P Pinus Strobus                                         
P Pinus Banksiana                                       
A Equus Caballus                                        
A Canis Lupus                                           
;                                                       

   /* Define a format named $kingatr                  */
   /* for 'kingdom attributes'                        */
proc format;                                            
   value $kingatr 'A' = 'Mobile, nonphotosynthetic'     
                  'P' = 'Immobile, photosynthetic'
                  'O' = 'Other';                  

proc print;                                       
   format kingdom $kingatr.;                      
   title 'Life-forms with kingdom attributes';    
run;


/**********************************************************************/


proc print data=sasuser.monolith;          
   title 'Listing of All Employees';       
   var com_1des div_name dep_name emp_name;
run;                                       


/**********************************************************************/


   /* Unnecessary if already in sorted order.         */ 
proc sort data=employee;                                 
   by dep_num;                                           

   /* Unnecessary if already in sorted order.         */ 
proc sort data=dept;                                     
   by dep_num;                                           

data empdep;                                             
   merge dept(keep=dep_num div_num dep_name)             
         employee (keep=dep_num emp_num emp_name);       
   by dep_num;                                           

   /* Preceding merge left EMPDEP in sorted order by  */ 
   /* DEP_NUM. If DIV_NUM increases monotonically     */ 
   /* with DEP_NUM, omit step. Unnecessary if         */ 
   /* already in sorted order.                        */ 
proc sort data=empdep;                                   
   by div_num;                                           

   /* Unnecessary if already in sorted order.         */
proc sort data=division;                                 
   by div_num;                                           

data emdpdi;                                             
   merge division(keep=div_num div_name com_name)        
         empdep (keep=div_num dep_name emp_name);        
   by div_num;                                           

data epicom;                                             
   merge company(keep=com_name com_1des)   
         emdpdi;                           
   by com_name;                            

proc print;                                
   var com_1des div_name dep_name emp_name;
run;                                       


/**********************************************************************/


proc sql;
select company.com_name, division.div_name, dept.dep_name,
       employee.emp_name                                  
   from company, division, dept, employee                 
   where company.com_name=division.com_name               
   and division.div_num=dept.div_num                      
   and dept.dep_num=employee.dep_num;


/**********************************************************************/


proc sql;                          
select division.div_num,           
avg(dep_dbud) label='Div Avg',     
freq(dep_dbud) label='Dep cnt'     
   from dept, division             
   where                           
      dept.div_num=division.div_num
      group by division.div_num    
   order by 2;

                     
/**********************************************************************/


data divdep;                                 
   merge division(keep=div_num)              
         dept(keep=div_num dep_num dep_dbud);
   by div_num;                               

proc means data=divdep noprint nway;         
   var dep_dbud;                             
   class div_num;                            
   output out=result mean=dep_dbud           
                     n=dep_num;              

proc sort data=result;                       
   by dep_dbud;                              

proc print data=result;         
   var div_num dep_dbud dep_num;
run;                            


/**********************************************************************/


proc means data=monolith noprint nway;       
   var dep_dbud;                             
   class div_num;                            
   output out=result mean=dep_dbud n=dep_num;

proc sort data=result;                       
   by dep_dbud;                              

proc print data=result;                      
   var div_num dep_dbud dep_num;             
run;                                         


/**********************************************************************/


proc sql;                       
   select dept.dep_num,         
   freq(emp_num) label='Emp cnt'
   from dept left join employee 
   on dept.dep_num = employee.dep_num
   group by dept.dep_num             
   order by 2 desc, dep_num;         


/**********************************************************************/


proc means data=monolith noprint nway;
   var  emp_num;                      
   class dep_num;                     
   output out=result n=emp_num;
       
proc sort data=result;           
   by descending emp_num dep_num;

proc print data=result;          
   var dep_num emp_num;          
run;                             


/**********************************************************************/


data empdep;                          
   merge dept(keep=dep_num)           
       employee(keep=dep_num emp_num);
   by dep_num;                        

proc means data=empdep noprint nway;  
   var emp_num;                       
   class dep_num;                     
   output out=result n=emp_num;       

proc sort data=result;                
   by descending emp_num dep_num;     

proc print data=result;               
   var dep_num emp_num;               
run;                                  


/**********************************************************************/