/**********************************/
/* 1 - Run SAS9 Code on SAS Viya! */
/**********************************/
 
/*****************************************/
/* a. REQUIRED - Specify the folder path */
/*****************************************/
 
/* This code will dynamically specify the project folder */     
/* REQUIRED - SAS program must be saved to the location */
/* REQUIRED - Valid only in SAS Studio */
%let fileName =  %scan(&_sasprogramfile,-1,'\/');
%let myPath = %sysfunc(tranwrd(&_sasprogramfile, &fileName,));
%put &=myPath;
 
 
/* You can also manually specify your path to the location you want to save the downloaded CSV file if the code above does not work */
%let path = &myPath;   /*-----Modify your path here if necessary - Example path: C:/user/documents/ */
 
/* View the path to download the CSV file in the log */
%put &=path;
 
 
 
/*********************************/
/* b. Download CSV file from SAS */
/*********************************/
 
/* SAS Viya documentation data sets URL */
%let download_url = https://support.sas.com/documentation/onlinedoc/viya/exampledatasets/home_equity.csv;
 
/* Download CSV file from the internet and save the CSV file in SAS */
filename out_file "&path/home_equity.csv";
proc http
    url="&download_url"
    method="get" 
    out=out_file;
run;
 
/* Import the CSV file and create a SAS table in the WORK library */
proc import datafile="&path/home_equity.csv" 
            dbms=csv 
	    out=work.home_equity
	    replace;
	guessingrows=1000;
run;
 
 
 
/*********************************/
/* c. Run SAS9 in Viya!          */
/*********************************/
 
/* Preview the SAS table */
proc print data=work.home_equity(obs=10);
run;
 
 
/* View column metatdata */
ods select Variables;
proc contents data=work.home_equity;
run;
 
 
/* View descriptive statistics */
proc means data=work.home_equity;
run;
 
 
/* View number of distinct values in specified columns */
proc sql;
	select count(distinct BAD) as DistinctBAD,	
               count(distinct REASON) as DistinctREASON,
               count(distinct JOB) as DistinctJOB,
               count(distinct NINQ) as DistinctNINQ,
	       count(distinct CLNO) as DistinctCLNO,
	       count(distinct STATE) as DistinctSTATE,
	       count(distinct DIVISION) as DistinctDIVISION,
	       count(distinct REGION) as DistinctREGION
	from work.home_equity;
quit;
 
 
/* View categorical column frequencies */
proc freq data=work.home_equity order=freq nlevels;
	tables BAD REASON JOB NINQ CLNO STATE DIVISION REGION / plots=freqplot missing;
run;
 
 
/* View missing values in the table */
 
/* a. Create a format to group missing and nonmissing */
proc format;
	value $missfmt 
	   ' '='Missing' 
	    other='Not Missing';
	value missfmt  
	    . ='Missing' 
	    other='Not Missing';
run;
 
/* b. Apply the format in PROC FREQ */
proc freq data=work.home_equity; 
	format _CHAR_ $missfmt.; /* apply format for the duration of this PROC */
	tables _CHAR_ / missing missprint nocum nopercent;
	format _NUMERIC_ missfmt.;
	tables _NUMERIC_ / missing missprint nocum nopercent;
run;
 
 
/* Find the mean of the following columns to use to replace missing values using PROC SQL */
proc sql;
	select round(mean(YOJ)) as MeanYOJ,
	       round(mean(MORTDUE)) as MeanMORTDUE,
	       round(mean(VALUE)) as MeanVALUE,
	       round(mean(DEBTINC)) as MeanDEBTINC
	into :YOJmean trimmed, 
	     :MORTDUEmean trimmed,
	      :VALUEmean trimmed,
             :DEBTINCmean trimmed
	from work.home_equity;
quit;
%put &=YOJmean &=MORTDUEmean &=VALUEmean &=DEBTINCmean;
 
 
/* Prepare the data */
data work.final_home_equity;
	set work.home_equity;
 
	/* Fix missing values */
	if YOJ = . then YOJ = &YOJmean;
	if MORTDUE = . then MORTDUE = &MORTDUEmean;
	if VALUE = . then VALUE = &VALUEmean;
	if DEBTINC = . then DEBTINC = &DEBTINCmean;
 
	/* Round column */
	DEBTINC = round(DEBTINC);
 
	/* Format columns */
	format APPDATE date9.;
 
	/* Drop columns */
	drop DEROG DELINQ CLAGE NINQ CLNO CITY;
run;
 
 
/* Check the final data for missing values */
proc freq data=work.final_home_equity; 
	format _CHAR_ $missfmt.; /* apply format for the duration of this PROC */
	tables _CHAR_ / missing missprint nocum nopercent;
	format _NUMERIC_ missfmt.;
	tables _NUMERIC_ / missing missprint nocum nopercent;
run;
 
 
/* Preview final data */
proc print data=work.final_home_equity(obs=10);
run;
 
 
/* Create a visualization */
title height=14pt justify=left "Current vs Default Loans";
proc sgplot data=work.final_home_equity;
     vbar BAD / datalabel;
run;
title;
 
 
/* Create a logistic regression model to predict bad loans */
proc logistic data=work.final_home_equity;
	class REASON JOB / param=REFERENCE;
	model BAD(event='1') = LOAN MORTDUE VALUE REASON JOB YOJ DEBTINC;
	store mymodel;
run;
 
/* Score the model on the data */
proc plm restore=mymodel;
	score data= work.final_home_equity
              out=work.he_score predicted lclm uclm / ilink; 
run;
 
/* Preview the scored data */
proc print data=work.he_score(obs=25);
run;

/*****************************************/
/* 2 - Check hardcoded paths             */
/*****************************************/
 
/* Old local path or SAS9 remote server path */
%let old_path = C:\workshop;
 
proc import datafile="C:/users/peter/home_equity.csv" 
	    dbms=csv 
	    out=work.new_table replace;
    guessingrows=1000;
run;
 
 
/* New path to data in SAS Viya */
/* You can use the path macro variable from the previous program */
%let path = &path; /* ----- modify path to your data on the Viya server. Example - /new_viya_path/user  */
 
proc import datafile="&path/home_equity.csv" 
	    dbms=csv 
	    out=work.new_table replace;
    guessingrows=1000;
run;

/******************************************************************/
/* 3 - Loading data into memory in CAS for distributed processing */
/******************************************************************/
 
/***************************************************************/
/* a. Connect the Compute Server to the distributed CAS Server */
/***************************************************************/
cas conn;
 
 
/**********************************************/
/* b. View data sources connected to SAS Viya */
/**********************************************/
 
/* View available libraries (data sources) to the SAS Compute server */
libname _all_ list;
 
/* View available caslibs (data sources) connected to the CAS cluster */
caslib _all_ list;
 
 
/*********************************************************/
/* c. View available files in a caslib on the CAS server */
/*********************************************************/
/* The samples caslib is available by default. It's similar to the SASHELP library on the Compute server */
 
proc casutil;
    list files incaslib = 'samples';
quit;
 
 
/********************************************************************************************/
/* d. Load a table into the distributed CAS server and view metadata of the in-memory table */
/********************************************************************************************/
proc casutil;
 
    /* Explicitly load a server-side file into memory (files can be a database table, or other file formats like CSV,TXT, PARQUET and more) */
    load casdata='RAND_RETAILDEMO.sashdat' incaslib = 'samples'
         casout='RAND_RETAILDEMO' outcaslib = 'casuser';
 
    /* View available in-memory tables in the Casuser caslib */
    list tables incaslib = 'casuser';
 
    /* View the contents of the in-memory table */
    contents casdata='RAND_RETAILDEMO' incaslib = 'casuser';
quit;
 
 
/*****************************************/
/* e. Drop a distributed in-memory table */
/*****************************************/
proc casutil;
    droptable casdata='RAND_RETAILDEMO' incaslib = 'casuser';
quit;
 
 
/*************************************/
/* f. Disconnect from the CAS server */
/*************************************/
cas conn terminate;

/**********************************************************/
/* 4 - Run DATA step in on the distributed CAS server     */
/**********************************************************/
/* NOTE: The data is small for training purposes          */
/**********************************************************/
 
/***************************************************************/
/* a. Connect the Compute Server to the distributed CAS Server */
/***************************************************************/
cas conn;
 
 
/**************************************************/
/* b. Explicitly load a file into memory into CAS */
/**************************************************/
 
/* Load the demo home_equity.csv client-side file from the SAS Viya example data sets website into the CAS server */
filename out_file url "https://support.sas.com/documentation/onlinedoc/viya/exampledatasets/home_equity.csv";
proc casutil;
    load file=out_file
         casout='home_equity' outcaslib = 'casuser';
quit;
 
/* Confirm the table was loaded into CAS */
proc casutil;
     /* View available in-memory distributed tables */
     list tables incaslib = 'casuser';
quit;
 
 
/********************************************************************/
/* c. Create a library reference to a caslib using the CAS engine   */
/********************************************************************/
libname casuser cas caslib = 'casuser';
 
 
/****************************/
/* d. Preview the CAS table */
/****************************/
title "Original Raw Data";
proc print data=casuser.home_equity(obs=10);
run;
title;
 
 
/*************************************************************************/
/* e. Run DATA step on the in-memory table in the distributed CAS server */ 
/*    and create a new in-memory CAS table                               */
/*************************************************************************/
 
/* Prepare the data using the distributed CAS server */
data casuser.final_home_equity;
	set casuser.home_equity end=end_of_thread; /* Use the END= option to view the processing by thread *.
 
	/* Fix missing values with means */
	if YOJ = . then YOJ = 9;
	if MORTDUE = . then MORTDUE = 73761;
	if VALUE = . then VALUE = 101776;
	if DEBTINC = . then DEBTINC = 34;
 
	/* Round column */
	DEBTINC = round(DEBTINC);
 
	/* Format columns */
	format APPDATE date9.;
 
	/* Drop columns */
	drop DEROG DELINQ CLAGE NINQ CLNO CITY;
 
	/* View number of rows processed on each thread (demo data, not all threads will be used) */
	if end_of_thread=1 then	
		put  'Total Available Threads for Processing: ' _NTHREADS_
             ', Processing Thread ID: '    _THREADID_ 
             ', Total Rows Processed by Thread: '  _N_ ;
run;
 
 
/*********************************/
/* f. Preview the new CAS table */
/*********************************/
title 'Prepared CAS table';
proc print data=casuser.final_home_equity(obs=10);
run;
title;
 
 
/*****************************************************************/
/* Continue to the next program without disconnecting from CAS   */
/*****************************************************************/

/********************************************************************/
/* 5 - New distributed PROCS for the CAS server                     */
/********************************************************************/
/* NOTE: Continue processing the final_home_equity CAS table from   */
/*       the previous program. Once the data is loaded in-memory    */
/*       it stays in-memory until dropped or the CAS session ends.  */
/********************************************************************/
 
/************************************/
/* a. Descriptive statistics in CAS */
/************************************/
proc mdsummary data=casuser.final_home_equity;
    output out=casuser.home_equity_summary;
run;
proc print data=casuser.home_equity_summary;
run;
 
 
/************************************************/
/* b. Frequencies in the distributed CAS server */
/************************************************/
proc freqtab data=casuser.final_home_equity;
    tables BAD REASON JOB STATE DIVISION REGION / plots=freqplot;
quit;
 
 
/************************************************/
/* c. Correlation in the distributed CAS server */
/************************************************/
proc correlation data=casuser.final_home_equity;
run;
 
 
/*********************************************************************************************************/
/* d. View the cardinality of the columns using CAS                                                      */
/*********************************************************************************************************/
/*  The CARDINALITY procedure determines a variable’s cardinality or limited cardinality in SAS Viya.    */
/*  The cardinality of a variable is the number of its distinct values, and the limited cardinality of a */
/*  variable is the number of its distinct values that do not exceed a specified threshold.              */
/*********************************************************************************************************/
proc cardinality data=casuser.final_home_equity
                 outcard=casuser.home_equity_cardinality maxlevels=250;
run;
proc print data=casuser.home_equity_cardinality;
run;
 
 
/********************************************************/
/* e. Logistic regression in the distributed CAS server */
/********************************************************/
/* Demo: Logistic Regression Modeling Using the LOGSELECT Procedure in SAS Viya */
/* https://video.sas.com/detail/video/5334372288001/logistic-regression-modeling-using-the-logselect-procedure-in-sas-viya */
 
/* Run a logistic regression using the distributed CAS server */
proc logselect data=casuser.final_home_equity;
    class REASON JOB / param=REFERENCE;
    model BAD(event='1') = LOAN MORTDUE VALUE REASON JOB YOJ DEBTINC; 
    store out=casuser.mymodel;
run;
 
/* Score the data using your model */
proc astore;
    score data=casuser.final_home_equity 
          rstore=casuser.mymodel
	  copyvars=BAD
          out=casuser.home_equity_scored;
quit;
 
/* Preview the scored data */
proc print data=casuser.home_equity_scored(obs=10);
run;
 
 
/*****************************************************************/
/* Continue to the next program without disconnecting from CAS   */
/*****************************************************************/

/********************************************************************/
/* 6 - Execute SQL in the distributed CAS server                    */
/********************************************************************/
/* NOTE: Continue processing the final_home_equity CAS table from   */
/*       the previous program. Once the data is loaded in-memory    */
/*       it stays in-memory until dropped or the CAS session ends.  */
/********************************************************************/
 
/* To run SQL in the distributed CAS server you must use FedSQL with the sessref = option and the CAS session name */
 
/* Simple LIMIT */
proc fedsql sessref=conn;
    select *
    from casuser.final_home_equity
    limit 10;
quit;
 
 
/* GROUP BY */
proc fedsql sessref=conn;
    select BAD, 
           count(*) as TotalLoans, 
           mean(MORTDUE) as avgMORTDUE, 
           mean(YOJ) as avgYOJ
    from casuser.final_home_equity  
    group by BAD;
quit;
 
 
 
/*****************************************************************/
/* Continue to the next program without disconnecting from CAS   */
/*****************************************************************/

/**********************************************************************************/
/* 7 - Use the native CAS language (CASL) for additional data types and actions   */
/**********************************************************************************/
/* NOTE: Continue processing the home_equity_final CAS table from                 */
/*       the previous program. Once the data is loaded in-memory                  */
/*       it stays in-memory until dropped or the CAS session ends.                */
/**********************************************************************************/
 
/* Use CASL data types */
proc cas;
	/* String */
	myString = 'Peter Styliadis';
	print myString;
	describe myString;
 
	/* Numeric */
	myInt = 35;
	print myInt;
	describe myInt;
 
	myDouble = 35.5;
	print myDouble;
	describe myDouble;
 
	/* List */
	myList = {'Peter', 'SAS', 37, 'Curriculum Development', {'Owen', 'Eva'}};
	print myList;
	describe myList;
 
	/* Dictionary */
	myDict = {Name='Peter', Age=37, Job='Curriculum Development', Children={'Owen', 'Eva'}};
	print myDict;
	describe myDict;
quit;
 
 
/* Use native CAS actions on the CAS server for MPP */
proc cas;
	/* View available files in a caslib */
	table.fileInfo / caslib = 'samples';
 
	/* View available in-memory CAS tables in a caslib */
	table.tableInfo / caslib = 'casuser';
 
	/* Reference the CAS table using a dictionary */
	tbl = {name="FINAL_HOME_EQUITY", caslib="casuser"};
 
	/* Preview 10 rows of the CAS table */
        table.fetch / table = tbl, to=10; 
 
	/* View the number of missing values and distinct values */
	simple.distinct / table = tbl;
 
	/* View descriptive statistics */
	simple.summary / table = tbl;
 
	/* View frequency values */
	cols_for_freq = {'BAD','REASON','JOB'};
	simple.freq / table = tbl, input = cols_for_freq;
quit;
 
 
/***********************************/
/* Disconnect from the CAS server  */
/***********************************/
cas conn terminate;