/******************************************************************************
 * Programme : Beispiele: Datenvorbereitung
 * Reference : BEISPI5B7F
 * Source    : https://www.wearecas.eu/en/sampleCode/BEISPI5B7F
 ******************************************************************************/

/* --- BLOC 1 --- */
proc contents data=Inventory; run;
proc contents data=Sales; run;
proc contents data=Sales2019; run;
quit;
proc sort data=inventory; by PartNumber; run;
proc sort data=Sales; by PartNumber; run;
proc sort data=Sales2019; by PartNumber; run;

proc print data=inventory; run;
proc print data=Sales; run;
proc print data=Sales2019; run;

/* --- BLOC 2 --- */
proc sql; 
   title "Variables Common to Inventory, Sales, and Sales2019";
   create table commonvars as                                       
      select memname, upcase(name) as name
             from dictionary.columns 
             where libname='WORK' and 
                   memname in ('INVENTORY', 'SALES', 'SALES2019');
   select name                                                      
          from commonvars
          group by name 
          having count(*)=(select count(distinct(memname)) from commonvars);
quit;

/* --- BLOC 3 --- */
proc sort data=sales;                     
   by partNumber;                                
run;         
proc print data=sales; run;                      

proc freq data = Sales noprint;                  
   tables partNumber / out = SalesDupes           
                     (keep = partNumber Count    
                      where = (Count > 1));     
run;                                             

proc print data=SalesDupes;run;

data SalesUnique;                                
   set Sales;                                    
   uniqueID = catx('.',partNumber,_n_);          
run;

proc print data=SalesUnique;                     
   var uniqueID partNumber partName salesPerson; 
run;

/* --- BLOC 4 --- */
data cars;                                     
   set sashelp.cars;    
run;

proc contents data=cars; run;                  
proc contents data=CarsSmall; run; 

data combineCars;
   merge cars CarsSmallNum;                    
   by make model;
   keep Make DriveTrain Model MakeModelDrive Weight;
run;

/* --- BLOC 5 --- */
proc print data=quarter1; run;
proc print data=quarter2; run;
proc print data=quarter3; run;
proc print data=quarter4; run;

data yearly;
   merge quarter1 quarter2 quarter3 quarter4;
   by Account;
run;

data yearly;                                   
   length Mileage 6;
   merge quarter1 quarter2 quarter3 quarter4;
   by Account;
run;
proc contents data=yearly; run;

/* --- BLOC 6 --- */
proc contents data=class; run;
proc contents data=classfit; run;

proc sort data=class; by name; run;
proc sort data=classfit; by name; run;

data merged;
   merge class classfit; by Name;
   attrib Weight
       label = "Weight";
   attrib Height Weight Predict format=comma8.2;
run;
proc print data=merged;
run;
proc contents data=merged; run;

/* --- BLOC 7 --- */
data vehicles(rename=(weight=weightLBS));
    set vehicles;
run;