asdf

/*Kod - projekt zaliczeniowy z przedmiotu Podstawowe i zaawansowane programowanie w SAS*/
/*(c) Karol Przanowski*/
/*kprzan@sgh.waw.pl*/

options compress=yes;

%let dir_projekt=/folders/myfolders/projektPD/ProjektVin/dane;

libname wej "&dir_projekt" compress=yes;

/*1 KROK - Vintage macro*/

/* Creating lists variables */
%let a = '1#2#3';
%let b = 'ins#css#12';

%macro vintage;
/* iterating through vins */
%do i = 1 %to 3;
/* iterating through credit types */
%do j = 1 %to 3;

%let due = %scan (&a,&i,'#');
%let prod = %scan (&b,&j,'#');

data vint&due._∏
set wej.Transactions;
seniority = intck('month', input(fin_period, yymmn6.), input(period, yymmn6.));
vin&due =(due_installments>=&due);
output;
/* counting vins according rto seniorities */
if status in ('B','C') and period<='200812' then do;
    n_steps=intck('month',input(period,yymmn6.),input('200812',yymmn6.));
        do i=1 to n_steps;
        period=put(intnx('month',input(period,yymmn6.),1,'end'),yymmn6.);
        seniority=intck('month',input(fin_period,yymmn6.),input(period,yymmn6.));
        output;
    end;
end;

keep aid product fin_period vin&due seniority;
run;

/* dividing into types of credits */
data vint&due._&prod;
set vint&due._&prod;
/* zastanawiam się nad porzuceniem tego 12 dla ograniczenia ewentualnego pszypału  */
if 12  = &prod then output;
else if product="&prod" then output;
drop product;
run;

/* chyab nie rozumiem po co to jest  */
/* counts how many times vin conditions are met, statistically, in every seniority and fin period combination  */
/* zmienilbym tu nazwę vintagr, jest niestandardowa i nie widze jej logiki  */
proc means data=vint&due._&prod noprint nway;
class fin_period seniority;
var vin&due;
output out=vintagr&due._&prod (drop=_freq_ _type_) n()=production mean()=vintage&due;
format vintage&due nlpct12.2;
run;

/* displaying number of credits given */
proc means data=vint&due._&prod noprint nway;
class fin_period;
var vin&due;
output out=production(drop=_freq_ _type_) n()=production;
where seniority=0;
run;

/* Preparing data for plotting vintages */
proc transpose data=vintagr&due._&prod out=vintage&due._&prod prefix=After_ suffix=_months;
by fin_period;
var vintage&due;
id seniority;
run;

data vintage&due._&prod;
set vintage&due._&prod;
drop _NAME_;
run;


/* filtering records with desired seniority */
/* tu bym duzo pokombinował, to nasze główne pole do popisu*/
data vin&due._&prod;
set vint&due._&prod;
if seniority in (3, 6, 9, 12);
run;

/* sprawdziłbym co tu się dzieje, duża szansa spytania nas o to */
/* data must be sorted to transpose it again */
proc sort data = vin&due._&prod out=vin&due._&prod;
by aid;
run;

/* transforming into table where one record represents one credit with its chosen seniorities vins */
proc transpose data=vin&due._&prod out=vin&due._&prod (drop=_name_) prefix=vin&due._;
by aid;
var vin&due;
id seniority;
run;

/* słabo rozumiem co tu się odbywa */
/* merging obtained information with production table */
proc sql noprint;
create table production&due._&prod as
select a.*, b.*
from wej.production a
left join vin&due._&prod b
on a.aid=b.aid;
quit;


/* dividing tables into credit types? */
data wej.production&due._&prod;
set production&due._&prod;
if product = "&prod" or 12 = &prod;
run;
%end;
%end;
%mend;



%vintage;


/* preparing macrovariables for tree method */
%let zb=wej.Production3_12;
%put &zb;
%let tar=vin3_12;
%put &tar;
/* poważnie bym sie zastanowil nad zmiana nazwy zmienne_int_ord*/
/* writing variables from productions into macrovariable zmienne_int_ord */

proc sql noprint;
select name
into :zmienne_int_ord separated by ' '
from dictionary.columns where libname='WEJ' and memname='PRODUCTION' and type='num';
quit;
/* number of variables in macrovariable */
%let il_zm = &sqlobs;

%put ***&il_zm***&zmienne_int_ord;

/* tree parameters */
/* tu znowu trzeba tym powachlować i potestować */
/*maksymalna liczba podzia��w minus 1*/
%let max_il_podz=2;
/*minimalna liczba obs w li�ciu*/
%let min_percent=3;

/*running tree.sas*/
/* trzeba dokładnie rozgryźc to source2, wszędzie jest, ja bym na ich miejscu o to pytał  */
%include "&dir.tree.sas" / source2;

/*Formatting Podzialy_int_niem into variable which can be passed into macrovariable*/
/* tą nazwę koniecznei trzeba zmienic, najlepiej na coś z angielskiegoi nie 'key' */
data wyj.warunek;
set wyj.Podzialy_int_niem;
low = scan(war,1,'<');
if substr(low,1,1) = 'n' then low = '1=1and';
if substr(low,1,3) ne '1=1' then low = catt(lowcase(zmienna),'>',low,'and');
high = scan(war,2,'=');
if high='' then high ='1=1';
else high = catt(lowcase(zmienna),'=<',high);
if substr(war,1,1) = 'n' then miss = cats('not missing(',lowcase(zmienna),')and');
warunek = catt(miss,low,high);
warunek = transtrn(warunek,'and',' and ');
warunek = transtrn(warunek,'=<',' =< ');
warunek = transtrn(warunek,'>',' > ');
klucz=catt(lowcase(zmienna),'#',grp,'#',warunek);
keep zmienna grp warunek klucz;
run;


/* saving formated data into macrovariable */
/* znowu trzeba zmienic nazwe  */
proc sql noprint;
select klucz into :warunki separated by '^'
from wyj.warunek;
quit;

%put &warunki;
%put &sqlobs;

/*defining boundaries for divideing columns*/
%macro kategoryzacja;
data wyj.vin_kat;
set wej.Production3_12;
%do i = 1 %to &sqlobs;
%let warunek = %scan(%scan(&warunki,&i,'^'),3,'#');
%let zmienna = %scan(%scan(&warunki,&i,'^'),1,'#');
%let grupa = %scan(%scan(&warunki,&i,'^'),2,'#');
/*dividing columns into groups  */
if &warunek then &zmienna=&grupa;
%end;
output;
run;
%mend;
%kategoryzacja;



/* finding Cramer output */
%let gr = act#ags#agr#app;
%macro vcram;
%do i = 1 %to 4;
%let j = %scan(&gr,&i,'#');
data wyj.vin_grp_&j;
set wyj.vin_kat;
keep vin3_12 &j.:;
run;

proc contents data=wyj.vin_grp_&j out=varlist_&j noprint;
run;

proc sql noprint;
select name into:zm_&j separated by '#'
from varlist_&j;
quit;
%let liczba=&sqlobs;

data vcram_&j;
length vcram _cramv_ 8 zmienna $30;
run;

%do k = 1 %to (&liczba-1);
%let zm=%scan(&&zm_&j,&k,'#');
proc freq data = wyj.vin_grp_&j noprint;
tables vin3_12*&zm /chisq;
output out = vcram_&zm cramv;
run;

data vcram_&zm;
set vcram_&zm;
zmienna = "&zm";
vcram = abs(_cramv_);
run;

data vcram_&j;
set vcram_&j vcram_&zm;
run;

%end;

/* sorting obtaied cramer outputs */
proc sort data = vcram_&j out = wyj.vcram_&j;
by descending vcram;
run;

/*taking highest 5 cramer outputs*/
data wyj.vcram_&j;
set wyj.vcram_&j (obs = 5);
run;

%end;
%mend;
%vcram;


/* ======================= PLOTTING =========================== */
proc sql;
create table wej.selected_v
as
select * from wyj.vcram_act
union all
select * from wyj.vcram_agr
union all
select * from wyj.vcram_ags
union all
select * from wyj.vcram_app;
quit;

proc sql;
select zmienna into :selected_v separated by '#'
from wej.selected_v;
quit;
%let ilosc_v = &sqlobs;
%put ***&selected_v***&ilosc_v***;


%macro vintage_gr;
%do a=1 %to &ilosc_v;
%let nazwa_v = %scan(&selected_v,&a,'#');
data wej.vin_grp;
set wyj.vin_kat (keep = &nazwa_v aid cid);
run;

proc sql noprint;
create table wej.liczba_kat as
select count(distinct &nazwa_v) as kategorie
from wej.vin_grp;
quit;

proc sql noprint;
select kategorie into :il_kat
from wej.liczba_kat;
quit;

proc sql;
create table wej.transactions_plus
as
select a.*,  b.*
from wej.transactions a
inner join
wej.vin_grp b
on a.aid=b.aid and a.cid=b.cid;
quit;

%do due = 1 %to 3;
%do kat = 1 %to &il_kat;

data vint_&due._&nazwa_v._&kat;
set wej.transactions_plus;
seniority = intck('month', input(fin_period, yymmn6.), input(period, yymmn6.));
vin&due =(due_installments>=&due);
if &nazwa_v = &kat;
output;

if status in ('B','C') and period<='200812' then do;
    n_steps=intck('month',input(period,yymmn6.),input('200812',yymmn6.));
        do i=1 to n_steps;
        period=put(intnx('month',input(period,yymmn6.),1,'end'),yymmn6.);
        seniority=intck('month',input(fin_period,yymmn6.),input(period,yymmn6.));
        output;
    end;
end;

keep aid &nazwa_v fin_period vin&due seniority;
run;

proc means data=vint_&due._&nazwa_v._&kat noprint nway;
class fin_period seniority;
var vin&due;
output out=vintagr&due._&nazwa_v._&kat (drop=_freq_ _type_) n()=production mean()=vintage&due;
format vintage&due nlpct12.2;
run;

proc transpose data=vintagr&due._&nazwa_v._&kat out=wyj.vintage&due._&nazwa_v._&kat prefix=After_ suffix=_months;
by fin_period;
var vintage&due;
id seniority;
run;

data wyj.vintage&due._&nazwa_v._&kat;
set wyj.vintage&due._&nazwa_v._&kat;
drop _NAME_;
run;

proc export data=wyj.vintage&due._&nazwa_v._&kat dbms=xlsx outfile='D:\SAS_projekt\proba projektu\vintage_kat' replace;
sheet=vin&due._&nazwa_v._&kat;
run;


%end;
%end;
%end;
%mend;
%vintage_gr;



%macro vintage_gr_char;
data wyj.vin_kat;
set wyj.vin_kat;
if app_char_branch='Radio-TV' then app_char_branch='RadioTV';
run;

proc freq data=wyj.vin_kat noprint;
tables app_char_branch /out=kat_znakowe;
run;

proc sql noprint;
select app_char_branch into :kat_znakowe separated by '#'
from kat_znakowe;
quit;

%let il_kat=&sqlobs;

data wej.vin_grp;
set wyj.vin_kat (keep = app_char_branch aid cid);
run;


proc sql;
create table wej.transactions_plus
as
select a.*,  b.*
from wej.transactions a
inner join
wej.vin_grp b
on a.aid=b.aid and a.cid=b.cid;
quit;

%do due=1 %to 3;
%do i=1 %to &il_kat;
%let kat = %scan(&kat_znakowe,&i,'#');

data vint_&due._app_char_branch_&kat;
set wej.transactions_plus;
seniority = intck('month', input(fin_period, yymmn6.), input(period, yymmn6.));
vin&due =(due_installments>=&due);
if app_char_branch = &kat;
output;

if status in ('B','C') and period<='200812' then do;
    n_steps=intck('month',input(period,yymmn6.),input('200812',yymmn6.));
        do i=1 to n_steps;
        period=put(intnx('month',input(period,yymmn6.),1,'end'),yymmn6.);
        seniority=intck('month',input(fin_period,yymmn6.),input(period,yymmn6.));
        output;
    end;
end;

keep aid app_char_branch fin_period vin&due seniority;
run;

proc means data=vint_&due._app_char_branch_&kat noprint nway;
class fin_period seniority;
var vin&due;
output out=vintagr&due._app_char_branch_&kat (drop=_freq_ _type_) n()=production mean()=vintage&due;
format vintage&due nlpct12.2;
run;

proc transpose data=vintagr&due._app_char_branch_&kat out=wyj.vintage_&due._app_char_branch_&kat prefix=After_ suffix=_months;
by fin_period;
var vintage&due;
id seniority;
run;

data wyj.vintage_&due._app_char_branch_&kat;
set wyj.vintage_&due._app_char_branch_&kat;
drop _NAME_;
run;

proc export data=wyj.vintage_&due._app_char_branch_&kat dbms=xlsx outfile='D:\SAS_projekt\proba projektu\vintage_kat' replace;
sheet=vin&due._app_char_branch_&kat;
run;
%end;
%end;
%mend;
%vintage_gr_char;




/*Analiza vintage zbiorczo i w podziale na grupy produkt�w - export excel*/
%macro vintage2;
%do i = 1 %to 3;
%do j = 1 %to 3;
%let kred = %scan(&b,&j,'#');

proc export data = vintage&i._&kred dbms=xlsx outfile  = 'C:\Users\patry_000\Desktop\Programowanie w SAS\PROJEKT KO�COWY\Vintage og�em i w grupach produktowych\vintagev3.xlsx' replace;
sheet = vintage&i._&kred;
run;

%end;
%end;
%mend;
%vintage2;


/* PROGNOZA dla Vintage3_12 */
PROC IMPORT OUT= WORK.a DATAFILE= "C:\Users\Admin\Desktop\podstawowe i zaawansowane programowanie i statystyka w SAS\Zeszyt1.xlsx"
            DBMS=xlsx REPLACE;
     SHEET="Arkusz1";
     GETNAMES=YES;
RUN;

data aa;
merge work.Vintage3_12 work.a;
by fin_period;
output;
run;

proc arima data=Aa;
identify var=production;
estimate p=2;
identify var=After_12_months crosscorr=production;
estimate p=1 q=1 input=production;
forecast lead=12 out=results;
run;
quit;