import saspy, os
print(saspy.__file__.replace('__init__.py', 'sascfg_personal.py'))

import saspy 
sas = saspy.SASsession(results='HTML')
sas

%%SAS
libname fp "/home/u61485743";
options obs= max;
options nosource;
*****************************************************************************;
********************************* Assignment 1 ***********************************;
*****************************************************************************;
*

data fp.fact_cars_reg / NOLIST;
	infile "/home/u61485743/sas_course/mia_cars_reg_*.txt" dsd flowover
	delimiter='|' eov=eov firstobs=2 ;

	length tozeret_cd $ 4 sug_degem_cd $ 1 baalut_cd $ 1 
	       sug_delek_cd $ 2 mispar_rechev $ 8 moed_aliya_lakvish $ 15;

	array col_to_upper sug_delek_cd baalut_cd; 

	input @; /* for every input line: read the line to check for eov flag */
	/* when new file starts : EOV turns to 1 */
	if eov=1 then input; /* then skips first input line (header) for the new file */
	/* else: regular input line */
	input mispar_rechev $ tozeret_cd $ sug_degem_cd $ ramat_eivzur_betihuty
	        kvutzat_zihum shnat_yitzur baalut_cd $ tzeva_cd sug_delek_cd $
	        moed_aliya_lakvish $ mivchan_acharon_dt: YYMMDD10.;
	

	format mivchan_acharon_dt YYMMDD10.;

	tozeret_cd = cat(repeat('0',4-length(tozeret_cd)-1),tozeret_cd);

	do over col_to_upper ;
	    	col_to_upper= upcase(col_to_upper);

	end;
 	eov= 0;  /* resets the eov flag */
	drop eov;
run;

proc contents data=fp.fact_cars_reg; 
run; 
/*  */
/* proc freq data=fp.fact_cars_reg; */
/* tables sug_delek_cd baalut_cd;  */
/* run; */

%%SAS
libname fp "/home/u61485743";
*****************************************************************************;
********************************* Assignment 2 ***********************************;
*****************************************************************************;

**** 1: adding new mispar_rechev col with the "-" notation;
**** 2: creating 2 cases: 7 chars "XXX-XX-XXX" mispar_rechev and 8 chars a "XX-XXX-XX"
**** 3: if 7 chars : concats without starting zero  ;

DATA fp.fact_cars_reg/ NOLIST;
    length mispar_rechev_fmt $ 10;
	set fp.fact_cars_reg;

	if substr(mispar_rechev,1,1) = "0" then do;
		mispar_rechev_fmt = cats(substr(mispar_rechev,2,2), "-" ,
             substr(mispar_rechev,4,3), "-", substr(mispar_rechev,7,2));
	end;

	else if length(mispar_rechev) = 8 then do;
		mispar_rechev_fmt = cats(substr(mispar_rechev,1,3), "-" ,
                            substr(mispar_rechev,4,2), "-",
                            substr(mispar_rechev,6,3));
	end;
run;

%%SAS
libname fp "/home/u61485743";
*****************************************************************************;
********************************* Assignment 3 ***********************************;
*****************************************************************************;

/*  palindroms precent for tozeret_cd over 1000 car count */;

/* strip*/
proc sql;
create table fp.palindroms as 
select  tozeret_cd,
		(a.count_palind / a.count_vehec) as percent_palindrom format percent8.3,
		 a.count_vehec,
		 a.count_palind
from 		(
               select tozeret_cd,
			    count(mispar_rechev) as count_vehec,
				case when count(mispar_rechev) >= 1000 then 1 else 0 end as check_1000,
				sum(case when compress(strip(mispar_rechev_fmt),"-") =
                   reverse(compress(strip(mispar_rechev_fmt),"-"))
		  	   		 then 1 else 0 end) as count_palind

				from fp.fact_cars_reg
				group by tozeret_cd
				having check_1000 =1
				)a
order by percent_palindrom desc;
quit;


proc print data=fp.palindroms (obs=20);
run;

%%SAS
libname fp "/home/u61485743";
*****************************************************************************;
********************************* Assignment 4 ***********************************;
*****************************************************************************;

/* importing 2 dim tables : xlsx and csv files
/* changing formats and lenghts
/* creating 3 dim tables by manual typing inputs -> datalines */

proc import datafile= "/home/u61485743/sas_course/dim_tozeret.xlsx"
			dbms = xlsx
			out= work.dim_tozeret_temp
			replace;
run;
/*  */
/*  this table is not avilable */
/* proc import datafile= "S:\workshop\MIA\mia_dict_colors.csv" */
/* 			dbms = csv */
/* 			out= work.dict_colors_temp */
/* 			replace; */
/* 			guessingrows= max; */
/* run; */
/*  */

proc sql;
create table fp.dim_tozeret as
select put(tozeret_cd ,z4.) as tozeret_cd , /* numeric to char */
	   put(tozeret_nm, $CHAR20.) as tozeret_nm /*char to char */
/*        put(tozeret_country_cd, $CHAR2.) as tozeret_country_cd /*char to char */
from dim_tozeret_temp;
quit;


/* create table fp.dim_tzeva as */
/* select tzeva_cd , /* numeric to char */
/* 	   put(tzeva_nm, $CHAR20.) as tzeva_nm,  /*char to char */
/*        put(tzeva_type, $CHAR10.) as tzeva_type /*char to char */
/* from dict_colors_temp; */
/* run; */
/*  */

data fp.dim_degem  / NOLIST;
	input +1 sug_degem_cd $1. sug_degem_nm $20. ;
	cards;
	P Private
	M Commercial
	;
run;


data fp.dim_baalut / NOLIST;
	input +1 baalut_cd $1.  baalut_nm $20. ;
	datalines;
	R Rent
	C Company
	L Leasing
	P Private
	D Dealer
	;
run;

/* exe sheet indicates length 1 to delek_cd ,
/* I've changed to length 2 to store all the input correctly */

data fp.dim_delek / NOLIST;
	input +1 delek_cd $2. delek_nm $20. ;
	datalines;
	G Gasoline
	L LPG
	D Diesel
	E Electric
	EG Electric\Gasoline
	ED Electric\Diesel
	;
run;

%%SAS
libname fp "/home/u61485743";
*****************************************************************************;
********************************* Assignment 5 ***********************************;
*****************************************************************************;

proc sql;
create table fact_cars_reg_flat_temp (drop= delek_cd) 
	as 
	select * 
	from fp.fact_cars_reg f
	left join fp.dim_tozeret d1
	on f.tozeret_cd = d1.tozeret_cd
/* 	left join fp.dim_tzeva d2 */
/* 	on f.tzeva_cd = d2.tzeva_cd */
	left join fp.dim_degem d3
	on f.sug_degem_cd = d3.sug_degem_cd 
	left join fp.dim_baalut d4
	on f.baalut_cd= d4.baalut_cd
	left join fp.dim_delek d5
	on f.sug_delek_cd= d5.delek_cd;
	quit;
run;

/* fill nulls in character and numeric columns */
/* using arrays to store the data and do-loops for less manual work */ 
data fp.fact_cars_reg_flat;
	set fact_cars_reg_flat_temp;
	array num_cols _numeric_; 
	array cat_cols _character_;

	do over cat_cols;
		if missing(cat_cols) 
            then cat_cols= "Unknown";
	end;

	do over num_cols;
		if missing(num_cols) 
            then num_cols =  -1 ; 
	end;
run;


proc report data=fp.fact_cars_reg_flat (obs=10);
run;

%%SAS
libname fp "/home/u61485743";
*****************************************************************************;
********************************* Assignment 6 ***********************************;
*****************************************************************************;


filename km "/home/u61485743/sas_course/mia_km_last_test.txt" ;
data fp.km_last_test;
	infile km
	delimiter=',' firstobs=2 ;
	length mispar_rechev $ 8;
	input mispar_rechev $ mivchan_aharon_km;
	mispar_rechev= cat(repeat('0',8-length(mispar_rechev)-1),mispar_rechev);
run;

proc sql outobs = 2000;
create table test_rand as 
select * from fp.fact_cars_reg
order by RANUNI (42);
quit;

proc sql outobs=50; 
select  f.mispar_rechev ,d.mispar_rechev 
		,d.mivchan_aharon_km 
from test_rand f
left join fp.km_last_test d
on f.mispar_rechev = d.mispar_rechev;
quit;

%%SAS
libname fp "/home/u61485743";

*****************************************************************************;
********************************* level 7 ***********************************;
*****************************************************************************;
/* 1 - joining km dim table to fact table 
       with calculation of:
						   -aliya_lakvish- start month and mivchan_achcron- end month (intnx functin)
                           -months between start month and end month (intck fucntion)
                           -avg km per month 
						   -values where calcualtions were not feasible replaced with nulls */
/* 2 - summary table of the above*/
/* 3 - distribution plot of the above (avg km per month) without outliers */
/* 4 - % of non-calculated (nulls, zeroes and negative values) vs calaculated of avg kp per month*/

proc sql  ;
create table  fp.fact_avg_km  as 
		select  d.mispar_rechev as mispar_rechev_dim , f.*
				,d.mivchan_aharon_km 
				,intnx('month',input(f.moed_aliya_lakvish , anydtdte.),1)  as start_date
        /* 'b' --> start of month */
				,intnx('month',mivchan_acharon_dt ,0,'b') as end_date
				,mivchan_acharon_dt
				,intck('month',calculated start_date ,calculated end_date,'d') as months_passed
			    ,mivchan_aharon_km  / intck('month',calculated start_date ,calculated end_date,'d') as avg_monthly_km_tmp
				,case when calculated months_passed > 0 and calculated avg_monthly_km_tmp > 0 
							then calculated avg_monthly_km_tmp end as avg_monthly_km

       from FP.FACT_CARS_REG_FLAT f
		left join fp.km_last_test d
		on f.mispar_rechev = d.mispar_rechev;
quit;


/* find number of nulls in any of the 2 tables - fact or dim  */
proc sql;
create table nulls_in_tables as 
		select 
			sum(case when f.mispar_rechev is null then 1 end) as num_nulls_in_dim,
			sum(case when mivchan_aharon_km is null then 1 end) as  num_nulls_in_fact
      from FP.FACT_CARS_REG_FLAT f
	  full join fp.km_last_test d
		on f.mispar_rechev = d.mispar_rechev;
quit;  /* no nulls in dim! */ 

proc print data= nulls_in_tables;
run;

/* summary table and distribution plot */
proc sql ;
create table avg_km_summary as
	select  mispar_rechev,
			put(start_date, YYMMDD10.) as start_date,
			put(end_date ,YYMMDD10.) as end_date,
            months_passed,
			avg_monthly_km
	from fp.fact_avg_km;
quit;

title "avg montly km distribution by count of cars";
proc sgplot data= avg_km_summary;
histogram avg_monthly_km /nbins=20 scale=count ;
density avg_monthly_km;
where avg_monthly_km > 0 and avg_monthly_km <5000;
run;
title;


/* non-calculated vs calculated */ 
proc sql;
create table fp.avg_km_agg as 
	select 
			count(*) as num_cars ,
			sum(case when  avg_monthly_km =. then 1 end) 
			/ count(*)  as percent_uncalculated foramt percent8.3,
			sum(case when  avg_monthly_km ~=. then 1 end)
			/ count(*) as percent_calculated foramt percent8.3,
			sum (case when months_passed <= 0 or months_passed is missing then 1 end) as negative_or_null_months, /*negative or months */
			sum(case when (mivchan_aharon_km <=0 or mivchan_aharon_km is missing) and months_passed>0 then 1 end) as months_without_km, /*months but no km */
			sum(case when avg_monthly_km is missing then 1 end) as total_missing
	from
	     fp.fact_avg_km ; 
quit;

proc report data= fp.avg_km_agg;
run;

%%SAS
libname fp "/home/u61485743";
*****************************************************************************;
********************************* level 8 ***********************************;
*****************************************************************************;

proc univariate data= fp.fact_avg_km ;
var avg_monthly_km;
run;


/* adding columns to lable the ouliers/ nob-outliers and aggregate the counts for each year */ 
proc sql;
create table fp.km_outliers as 
select  *, avg(avg_monthly_km),
		 median(avg_monthly_km),
		 STD(avg_monthly_km),
		count(*) as total_count,
		case when  shnat_yitzur = 2018   
	        	and avg_monthly_km >3000 then "outlier" 
		when  avg_monthly_km <=3000 then "non-outlier" end as is_outlier_2018,
		sum(case when calculated is_outlier_2018 = "outlier" then 1 end) as count_outlier_2018,
		sum(case when calculated is_outlier_2018 = "non-outlier" then 1 end) as count_non_outlier_2018,

				case when  shnat_yitzur = 2019   
	        	and avg_monthly_km >3000 then "outlier" 
		when  avg_monthly_km <=3000 then "non-outlier" end as is_outlier_2019,

				case when  shnat_yitzur = 2020  
	        	and avg_monthly_km >3000 then "outlier" 
		when  avg_monthly_km <=3000 then "non-outlier" end as is_outlier_2020,		

		sum(case when calculated is_outlier_2019 = "outlier" then 1 end) as count_outlier_2019,

		sum(case when calculated is_outlier_2020 = "outlier" then 1 end) as count_outlier_2020

from fp.fact_avg_km 
where avg_monthly_km is not missing
and sug_degem_cd= 'P'
;
quit;

/* creating a macro function the will loop over the attributes we want to check and creates plots */ 

/*ods excel file='S:\workshop\output\report.xlsx';*/
ods graphics on;
%macro outlier_report(namer);
 %do i= 1 %to 6;

 /*creating a pivot table for each car attribute - outlier/non-outluer/total population precentage  */
  proc sql outobs=10;
    create table agg_by_category_&i as
		select 
				%scan(&namer,&i),
				count(*) / total_count format percent10.2 as tot_pop, 
				sum(case when is_outlier_2018 = 'non-outlier' then 1 end) / count_non_outlier_2018 format percent10.2 as non_outlier, 
				sum(case when is_outlier_2018 = 'outlier' then 1 end) / count_outlier_2018 format percent10.2 as outlier_2018,
				sum(case when is_outlier_2019 = 'outlier' then 1 end) / count_outlier_2019 format percent10.2 as outlier_2019, 
				sum(case when is_outlier_2020 = 'outlier' then 1 end) / count_outlier_2020 format percent10.2 as outlier_2020
					
		from fp.km_outliers 
		where %scan(&namer,&i) is not missing
		group by %scan(&namer,&i), total_count, count_non_outlier_2018  ,count_outlier_2018 , count_outlier_2019 , count_outlier_2020 
		order by tot_pop desc;
    quit;
	
	ods layout start columns =2 rows =1; 
	/* plotting the pivot table */
ods region row= 1 column= 1;
	proc sgplot data = agg_by_category_&i;
	     xaxis label= ' ';
	 	hbar %scan(&namer,&i) / response= outlier_2018  ;
		hbar %scan(&namer,&i) / response= non_outlier barwidth=0.5;
	run;

	/* adding the data set table */
	proc print data= agg_by_category_&i (drop=outlier_2019 outlier_2020);
	run;


	proc sort data=agg_by_category_&i;
	by %scan(&namer,&i);
	run;

	/* unpivot with transposing in order to plot attributes of outliers by year */ 
	proc transpose data= agg_by_category_&i out= AGG_BY_CATEGORY_T_&i;
	by %scan(&namer,&i);
	var outlier_:;
	run;
	
	/* chnaging column name and types after transposition */
	data AGG_BY_CATEGORY_T2_&i;
		set AGG_BY_CATEGORY_T_&i;
		time_col = input(substr(_NAME_  ,9,4), 5.);
		percent_outliers= col1;
		format percent_outliers percent7.3;
	run;

	/* plotting a line plot for each category of the attribute over the years  */
ods region row= 1 column= 2;
	proc sgplot data= AGG_BY_CATEGORY_T2_&i;
	series x= time_col   y=percent_outliers / group = %scan(&namer,&i) curvelabel ;
	run;
	/* adding the data set table */
	proc print data= agg_by_category_&i (drop= non_outlier tot_pop);
	run;
	ods layout end;
 %end;

%mend;

/*car attributes to check */
%let namer = delek_nm baalut_nm kvutzat_zihum tozeret_nm ramat_eivzur_betihuty tzeva_cd;

/*using the macro function */
%outlier_report (&namer);

%%SAS
libname fp "/home/u61485743";

*****************************************************************************;
********************************* assignment 9 ***********************************;
*****************************************************************************;

data fp.dim_baalut_hist;
infile "S:\workshop\MIA\mia_baalut_hist.csv" 
	delimiter=',' firstobs=2 ;

	length baalut_cd $ 1;
	input mispar_rechev $ baalut_start_dt : YYMMN. baalut_cd $ ;
	format baalut_start_dt date9.;
run;


proc contents date=fp.dim_baalut_hist;
run;





*****************************************************************************;
********************************* assignment 10 ***********************************;
*****************************************************************************;

/* discalimer: 
/* final calculation of AVG baalut months does not take into considiration
/* the last transaction of a mispar_rechev in the case baaulut equals 'D'
/* since there is no way to estimate correctly last period of baalut in that case */
/* which is still on-going (to my understanding)*/

proc sort data= FP.DIM_BAALUT_HIST;
by  mispar_rechev descending baalut_start_dt;
run;


/* add a "lead"  column to date of baalut change 
/* and calculate diff in months by using lag in desc order */

data FP.DIM_BAALUT_HIST_LAG;
set FP.DIM_BAALUT_HIST;
by mispar_rechev;
	next_change_dt = lag1(baalut_start_dt);  
if first.mispar_rechev then next_change_dt=. ; 	
format next_change_dt date9. ;

month_gap= intck('month', baalut_start_dt ,next_change_dt);
run;

/* subquery-> 2 aggregations in the car granularity:  */
/* first: count rows where "D" value, gives 1 if larger than 0
/* second : count months in the dealer state per car */

/* outer query-> 2 aggregation in the yazran granularity : 
/*  avg on months in the dealer state
/* and count cars that had "D" state  */

proc sql; 
	create table FP.FACT_CARS_REG_FLAT2 as
		select tozeret_nm, 
				count(mispar_rechev) as count_cars_dealer_state,
				avg(sum_months_in_D_state) foramt 6.2 as avg_months_in_Dealer_state
		from
			(
/*		proc sql inobs=100;*/
			select h.mispar_rechev, tozeret_nm, 
			  	  case when (sum(case when h.baalut_cd = 'D' then 1 end )) >= 1 then 1 end as is_d,
			      SUM(case when h.baalut_cd = 'D' then month_gap end) as sum_months_in_D_state 
			from FP.DIM_BAALUT_HIST_LAG as h
			left join FP.FACT_CARS_REG_FLAT as f
			on f.mispar_rechev = h.mispar_rechev
			group by h.mispar_rechev, tozeret_nm
			)a
		group by tozeret_nm
        order by count_cars_dealer_state desc;
quit;


proc print data= FP.FACT_CARS_REG_FLAT2;
run;

*****************************************************************************;
********************************* assignment 11 ***********************************;
*****************************************************************************;

proc sql;
create table multi_baalut as 
	select mispar_Rechev,
			count (distinct baalut_cd) as num_unique_baalut,
			sum (case when baalut_cd = 'P' then 1 end) as count_P,
			sum (case when baalut_cd = 'C' then 1 end) as count_C,
			sum (case when baalut_cd = 'D' then 1 end) as count_D,
			sum (case when baalut_cd = 'L' then 1 end) as count_L ,
			sum (case when baalut_cd = 'R' then 1 end)as count_R ,
			count(*) as count_num_baalut
	from
	FP.DIM_BAALUT_HIST
	group by mispar_Rechev;
quit;

/* filter and count cars with only 3+ baalut type*/
proc sql;
 create table fp.count_3_plus_baalut as 
select count(*) as num_cars_3_plus_baalut
from 
	(select* 
	from multi_baalut
	where num_unique_baalut >= 3) a
	;
quit;

proc print data=fp.count_3_plus_baalut;
run;

/* first method for finding outliers : computing precentiles with proc means */

/* create a stats table to find limit for extreme values -> 99th precentile */ 
 proc means  data= multi_baalut n mean stddev p75 p95 p99 nmiss max STACKODS ;
var _numeric_;
/*output out= means_baalut p99= /autoname;*/
ods select all;
ods output fp.means_baalut= _all_;
run;

/* all the cars that have a higher stat than the 99th precentile's value
in any of the stats, should be considered as ouliers or "suspicous"  */
proc sql;
create table fp.ouliers_baaulut1 as
select *
from multi_baalut
where count_p >3 or
	count_C >2 or
	count_D >4 or
	count_L >1 or
	count_R >1 or
    num_unique_baalut>3 or
	count_num_baalut > 5;
quit;

/* second method for finding outliers : robust regression with MCD algorithm  */

/*  robust regression finds the center of the data and calculstes 
 distances for each observation with the multi covariance detrerminant . 
extreme outliers will have the largest distance */

/* first step:  fill missing valuees with 0 */
proc sql;
create table multi_baalut2 as 
	select mispar_Rechev,
			count (distinct baalut_cd) as num_unique_baalut,
			sum (case when baalut_cd = 'P' then 1 else 0 end) as count_P,
			sum (case when baalut_cd = 'C' then 1 else 0 end) as count_C,
			sum (case when baalut_cd = 'D' then 1 else 0 end) as count_D,
			sum (case when baalut_cd = 'L' then 1 else 0 end) as count_L ,
			sum (case when baalut_cd = 'R' then 1 else 0 end)as count_R ,
			count(*) as count_num_baalut
	from
	FP.DIM_BAALUT_HIST
	group by mispar_Rechev;
quit;


/* step 2 : adding y column . y is a normal distribution random value so should not effect computation  */ 

data multi_baalut2;
set multi_baalut2;
y= rand('normal', -1, 1);
retain indexer 0;
indexer = indexer +1;
run;

/* step 3 : using proc robustreg with mcd output */ 
/* leverage = X space oulier */ 

/*/ods exclude all;*/
proc robustreg data= multi_baalut2 method = M seed=12345;
model y=count_p count_c count_d count_l count_r
         count_num_baalut num_unique_baalut/ diagnostics leverage(MCDInfo);
ods select MCDenter MCDcov Diagnostics;
ods output diagnostics= Diagnostics(where=(leverage=1));
run;
/*performance threads;*/


proc sort data= diagnostics out= diagnostics;
by descending PRobustDist;
run;


/* step 4:  combine results for outliers with initial table */ 
proc sql  ;
create  table fp.multi_baalut_mdc (drop= y indexer) as 
select m.* , d.PRobustDist
from multi_baalut2 m
left join diagnostics d
on m.indexer-2= d.obs
order by d.PRobustDist desc;
quit;

%%SAS 
proc sql;
select * from fp.import;
quit;