/************** Setup/Connection Details **************/

%let repopath=c:\Desktop;       * Location of madlib-sas-macros repo;
%let engine=greenplm;           * SAS/Access engine (greenplm, hawq or postgres);
%let server='XXX.XXX.XXX.XXX';  * database server/host IP address;
%let port=5432;                 * database port number;
%let database='public';         * database name;
%let schema=sasgpdb;            * database schema;
%let user='username';           * database credentials - username;
%let password='password';       * database credentials - password;


/************** Establish libref to database **************/

* Establish libref to database;
libname mydblib &engine. server=&server. port=&port. database=&database. user=&user. password=&password. schema=&schema.;


/************** Data preparation **************/

* Copy SAShelp 'cars' dataset to database for testing;
proc sql;
  CREATE TABLE mydblib.cars AS
  SELECT *
  FROM sashelp.cars;
quit;


/************** Run MACROS files **************/

%macro include_files;

  * Determine operating system and set correct / or \ for filenames;
  %let del=/; * UNIX OS;
  %if &sysscp=WIN %then %let del=\; * Windows OS;

  * linear regression MACROs;
  %include "&repopath.&del.madlib-sas-macros&del.linear-regression&del.madlib_sas_publish_linreg.sas";
  %include "&repopath.&del.madlib-sas-macros&del.linear-regression&del.madlib_sas_score_linreg.sas";

  * logistic regression MACROs;
  %include "&repopath.&del.madlib-sas-macros&del.logistic-regression&del.madlib_sas_publish_logreg.sas";
  %include "&repopath.&del.madlib-sas-macros&del.logistic-regression&del.madlib_sas_score_logreg.sas";

%mend include_files;
%include_files;

/************** Linear Regression **************/

* Linear regression model predicting mpg_highway;
proc reg data=sashelp.cars;
  model mpg_highway=Horsepower Weight EngineSize;
  * Output parameter estimates to a dataset;
  ods output parameterEstimates = sas_mpg_highway_lrm;
run;
quit;

* Run model publishing macro;
%madlib_sas_publish_linreg(
  modelDataset=sas_mpg_highway_lrm
 ,modelTable=&schema..db_mpg_highway_lrm
 ,server=&server
 ,db=&database
 ,port=&port
 ,user=&user
 ,password=&password
 ,drop=1
);

* Run model scoring macro;
%madlib_sas_score_linreg(
  inTable=&schema..cars
 ,outTable=&schema..cars_scored_lrm
 ,modelTable=&schema..db_mpg_highway_lrm
 ,server=&server
 ,db=&database
 ,port=&port
 ,user=&user
 ,password=&password
 ,predictColumnName=predict
 ,residualColumnName=err
 ,drop=1
);

* View results;
proc print data=mydblib.cars_scored_lrm;
run;


/************** Logistic Regression **************/

* Prep data for logistic - create binary column (BMW or Mercedes-Benz then yes) from make column;
proc sql;
  CREATE TABLE work.cars_binary AS
  SELECT *
        ,CASE WHEN make IN ('BMW','Mercedes-Benz') THEN 1 ELSE 0 END AS bmw_mb_flag
  FROM sashelp.cars;
quit;

* Logistic regression model predicting whether car is BMW or Mercedes-Benz;
proc logistic data=work.cars_binary;
  model bmw_mb_flag (event='1') = msrp enginesize cylinders weight;
  ods output parameterEstimates = sas_bmw_mb_flag_lgrm;
run;
quit;

* Run model publishing macro;
%madlib_sas_publish_logreg(
  modelDataset=sas_bmw_mb_flag_lgrm
 ,modelTable=&schema..db_bmw_mb_flag_lgrm
 ,server=&server
 ,db=&database
 ,port=&port
 ,user=&user
 ,password=&password
 ,drop=1
);

* Run model scoring macro;
%madlib_sas_score_logreg(
  inTable=&schema..cars
 ,outTable=&schema..cars_scored_lgrm
 ,modelTable=&schema..db_bmw_mb_flag_lgrm
 ,server=&server
 ,db=&database
 ,port=&port
 ,user=&user
 ,password=&password
 ,predictColumnName=predict
 ,drop=1
);

* View results;
proc print data=mydblib.cars_scored_lgrm;
run;