Implement ETL Process in SAS

The program below will run the whole process in a sequence which may be considered as a representation of ETL Process in SAS. The ETL process could also be set up in SAS ETL Studio or SAS Warehouse Administrator which would be far more sophisticated solution.

Lesson assumptions and objectives:

  • We are ready to execute the complete SAS program flow in a sequence
  • After running the loading, transformation and extraction programs, we will have a look at the data in sas using PROC MEANS procedure.
  • We will check sum, average, min & max values of all the measures and make sure that the sales data corresponds to out business scenario and apply our business assumptions


    Please refer to the comments (in green) included in the SAS program attached below.

    * run all programs in an ETL sequence ; %include 'D:\business_scenario\1-read_dimensions.sas'; %include 'D:\business_scenario\2-gen_facts_date_ids.sas'; %include 'D:\business_scenario\3-gen_facts_measures.sas'; %include 'D:\business_scenario\4-create-fact-table.sas'; %include 'D:\business_scenario\5-gen-costs.sas';   * Use PROC MEANS to analyze populated figures ; * We will check sum, average, min & max values of all measures ; * the MAXDEC parameter indicates that we want to limit numbers to 2 decimal places ;   PROC MEANS data=CognosBI.Sales_Facts mean min max sum MAXDEC=2; class prod_type prod_age; var price revenue quantity ; RUN;



    Summary and statistics for newly generated measures:
    Summary and statistics for newly generated measures

    <-- 5. Costs fact tableTutorial Index