/* komentarz */
/* przypisanie biblioteki */
libname s "C:\Users\STUDENT\Desktop\SBI" ; /*tworze biblioteke s w miejscu na pulpicie */
/* jak uruchomic - F3/F8 */
/* 4GL */

/* 1 */
/* Pobierz dane z sashelp.cars, gdzie pojazd to audi i zapisz w nowej tabeli w bibliotece S */

data s.audi ; /* utworz tabele */
set sashelp.cars ; /* set - pobierz z */
where make = 'Audi' ; /* warunek */
/* where upcase(make) = 'AUDI' ; -  wielkie znaki */
run; /* koniec bloku kodu do pzetworzenia */


/* 2 */
/* Pobierz dane z sashelp.cars, gdzie pojazd to audi lub BMW. W nowej tabeli w bibliotece S zapisz tylko 3 kolumny: make model invoice */

data s.audi ; /*(keep = make model invoice) - druga opcja keep */
set sashelp.cars ;
where upcase(make) in ('AUDI', 'BMW') ;
/* where upcase(make) = 'AUDI' or upcase(make) = 'BMW' ; -  wielkie znaki */
keep make model invoice ; /* drop */
run;

/* 3 */
/* if then else */
/* Pobierz dane z sashelp.cars i w zalezności od kolumny invoice utwórz kolumne kategoria_cenowa ('niska,'średnia','wysoka') */

data s.kategoria_cenowa ;
set sashelp.cars ;
length kategoria_cenowa $7. ; /* ustalenie długości znaków w tabeli */
if Invoice<30000 then kategoria_cenowa='niska' ;
else if Invoice<60000 then kategoria_cenowa='średnia' ;
else kategoria_cenowa='wysoka' ;
keep make model invoice kategoria_cenowa;
run;

/* 4 */
/* Wybiez najtansze auto z tabeli cars */
/* konieczne sortowanie tabeli */

proc sort data = sashelp.cars out = s.cars_sorted ;
/* by descending Invoice ; - malejacy */
by Invoice ; /* domyslne jest ascending */
run;


data s.najtansze ;
set s.cars_sorted ;
if _N_ = 1 ; /* _N_ - numerator */
run;

/* 5 */
/* Podaj wartosc wszystkich pojazdow w tabeli wedlug kolumny Invoice */

data s.wart_pojazdow ;
set sashelp.cars end = koniec;
wartosc + invoice ;
if koniec=1 then output; /* jesli prawda to zrzut */
keep wartosc ;
run;

/* 6 */
/* Przetwarzanie w grupach */
/* na podstawie tabeli cars zlicz pojazdy dla kazdej marki */
/* dane musza byc posortowane wedlug kolumny do grupowania */

proc sort data = sashelp.cars out = s.cars_sorted ;
by make ; /* sortowanie od A do Z */
run;

data s.liczba_pojazdow ;
set s.cars_sorted;
by make ; /* group by */
if first.make then liczba_pojazdow=0 ;
liczba_pojazdow + 1 ;
if last.make then output;
keep make model liczba_pojazdow;
run;

/* 7 */
/* Do danych z zad 6 podaj wartosc pojazdow */

data s.liczba_pojazdow_v2 ;
set s.cars_sorted;
by make ; /* group by */
if first.make then liczba_pojazdow=0 ;
if first.make then wartosc=0 ;
liczba_pojazdow + 1 ;
wartosc + invoice ;
if last.make then output;
keep make liczba_pojazdow wartosc;
run;

/**************************************************/
/* 08/10/2018 */
/**************************************************/

/* 1 */
/* na podstawie tabeli cars utwórz kolumnę nowa_cena,
która stanowi 90% wartości z kolumny invoice
wartości proszę zaokąglić do 2 miejsc po przecinku
*/

data s.nowa_cena ;
set sashelp.cars ;
nowa_cena = ROUND(invoice * 0.9,0.01) ;
format nowa_cena dollar12.2; /* .2 - dwie wartosci po przecinku, 12 - liczba bajtow */
keep make model invoice nowa_cena ;
run ;

/* 2 */
/* Daty w SAS */

data s.dzis ;
dzis = today() ;
/* format dzis ddmmyy10. ; */
format dzis date9. ;
wiek = dzis - '19FEB1996'd ; /*format musi pasowac, podawanie daty jako stalej w kodzie,  d - format daty */
dzien = day(dzis);
dzien_ty = weekday(dzis);
mies = month(dzis);
rok = year(dzis);
kw = qtr(dzis);
run ;

/* 3 */
/* Łączenie tabel w 4GL */

data s.tabela_A ;
do id = 1 to 1000 ;
kolumna_A = 'AAA' ;
output ; /* output musi być przed endem bo po każdej iteracji muszę zrzucić */
end ;
run ;


data s.tabela_B ;
do identyfikator = 1 to 1500 by 10 ;
kolumna_B = 'BBB' ;
output ; /* output musi być przed endem bo po każdej iteracji muszę zrzucić */
end ;
run ;

/* do łączenia MERGE */
/* klucze(kolumny) do łączenia muszą mieć taką samą nazwę */
/* tabele do łączenia muszą być posortowane po kolumnach kluczach */

/* najpiew trzeba posortować */

proc sort data =  s.tabela_A;
by id ;
run ;

proc sort data =  s.tabela_B;
by identyfikator ;
run ;

/* łączenie */

data s.merge_tabela_AB ;
merge s.tabela_A ( in = a ) /* in - alias tabeli */
      s.tabela_B ( rename = (identyfikator=id) in = b ) ; /* spełnienie wymogu, że klucze do kolumny mają tę samą nazwę */
by id ; /* klucz łączenia */
/* if a=b ; /* typ złączenia - inner join , część wspólna */
/* if a=1 ; /* złączenie left join */
if a=1 and b = 0 ;
/* if a=1 or b=1 ; /* suma */
run ;

/* biblioteka B */
libname B "C:\Users\STUDENT\Desktop\BIBL" ;

/* 4 */
/* Ustal czytelników, którzy posiadają nieodanne książki
podaj imie, nazwisko, adres, liczbe przetrzymywanych książek */

/*Selekcja nioddanych książek */
data B.nieoddane ;
set b.wypozyczenia ;
where DATA_ODDANIA = . ;
by czytelnik_id ;
if first.czytelnik_id then licz_nieoddane = 0 ;
licz_nieoddane +1 ;
if last.czytelnik_id ;
keep czytelnik_id licz_nieoddane ;
run ;

/* Łączenie tabel */

data b.nieoddane_czytelnicy ;
merge b.CZYTELNIK (in=c)
      b.nieoddane (in=n rename=(czytelnik_id=id)) ;
by id ;
if c=n;
keep CZYTELNIK_NAZWISKO CZYTELNIK_IMIE ADRES licz_nieoddane;
run ;

/* 5 */
/* Ustal 5 najczęściej wypożyczanych tytułów */

proc sort data=b.WYPOZYCZENIA ;
by ksiazka_sygnatura ;
run ;

data b.wyp_ks ;
set b.wypozyczenia ;
by ksiazka_sygnatura ;
if first.ksiazka_sygnatura then licz_ks=0;
licz_ks + 1;
if last.ksiazka_sygnatura ;
keep ksiazka_sygnatura licz_ks ;
run ;

data b.najczesciej_wybierane ;
merge b.KSIAZKA (in=k)
      b.wyp_ks ( in=wk rename=(ksiazka_sygnatura=sygnatura)) ;
by sygnatura ;
if k=wk ;
keep AUTOR_NAZWISKO tytul sygnatura licz_ks ;
run ;

proc sort data=b.najczesciej_wybierane ;
by tytul ;
run ;

data b.najczesciej_wybierane ;
set b.najczesciej_wybierane ;
by tytul ;
if first.tytul then licz_wypozyczenia=0;
licz_wypozyczenia + licz_ks ;
if last_tytul ;
keep tytul AUTOR_NAZWISKO licz_wypozyczenia ;
run ;

proc sort data=b.najczesciej_wybierane ;
by descending licz_wypozyczenia ;
run ;

data b.top_5 ;
set b.najczesciej_wybierane ;
if _N_<=5 or licz_wypozyczenia>=8 ;
run ;

/* parametr call symput */

/* 6 */
/* Dla każdego czytelnika podaj liczbę wypożyczonych ksiązek
w kazdym kwartale 2007 roku */
/* tabela ma mieć 5 kolumn i 58 wierszy(czytelników) */

data b.wyp_qtr ;
set b.wypozyczenia ;
kw1 = qtr(DATA_WYPOZYCZENIA)=1 ;
kw2 = qtr(DATA_WYPOZYCZENIA)=2 ;
kw3 = qtr(DATA_WYPOZYCZENIA)=3 ;
kw4 = qtr(DATA_WYPOZYCZENIA)=4 ;
run ;

proc sort data=b.wyp_qtr ;
by czytelnik_id ;
run ;

data b.wyp_qtr ;
set b.wyp_qtr ;
by czytelnik_id ;
if first.czytelnik_id then do ;
wypozyczenia_kw1=0;
wypozyczenia_kw2=0;
wypozyczenia_kw3=0;
wypozyczenia_kw4=0;
end ;
wypozyczenia_kw1 + kw1 ;
wypozyczenia_kw2 + kw2 ;
wypozyczenia_kw3 + kw3 ;
wypozyczenia_kw4 + kw4 ;
if last.czytelnik_id ;
keep czytelnik_id wypozyczenia_kw1-wypozyczenia_kw4 ;
run ;

/* 7 */
/* f. tekstowych */
/* płeć na podstawi ostatniej litery imienia */
data b.plec ;
set b.czytelnik ;
/* ost_litera=substr(CZYTELNIK_IMIE,5,3) ; /* Czytanie od 5 znaku przez 3 kolejne */
ost_litera=substr(CZYTELNIK_IMIE,length(CZYTELNIK_IMIE),1) ; /* Ostatnia litera imienia */
if ost_litera='a' then plec='K' ;
else plec='M' ;
keep CZYTELNIK_IMIE CZYTELNIK_NAZWISKO plec;
run ;

/**************************/
/* 15.10.2018 */
/**************************/

/*1*/
/* Wyznacz 5 czytelników z największą liczbą nieoddanych książek,
Podaj: imię, nazwisko, adres i liczbę nieoddanych książek */

proc sort data = b.wypozyczenia;
by czytelnik_id;
run;

data b.nieoddane;
set b.wypozyczenia ;
where data_oddania = .;
by czytelnik_id;
if first.czytelnik_id then licz_ks=0;
licz_ks+1;
if last.czytelnik_id;
keep czytelnik_id licz_ks;
run;

proc sort data = b.nieoddane;
by descending licz_ks ;
run;

data b_nieoddane_top5;
set b.nieoddane;
if _N_=5 then call symput('N5', licz_ks);
run;

data b.nieoddane_top5;
set b.nieoddane_top5;
where licz_ks>=&N5; /* & - macro parametr */
run;

/* zrobic łączenie z tabelą czytelnik */

/*******************************/
/* SAS SQL */
/******************************/

proc sql;
create table b.auta as
select *
from sashelp.cars
where make in ('Audi','BMW');

/*1*/
/* Na podstawie tabeli cars utwórz kolumnę nowa_cena,
która stanowi 90% wartości z kolumny invoice,
wartości zaokrąglić do 2 miejsc po pzecinku */

proc sql;
create table b.nowa_Cena as
select make, model, invoice,
ROUND(invoice*0.9,0.01) as nowa_cena format dollar12.2
from sashelp.cars;

/*2*/
/* Zlicz ilość i wartość pojazdów w ramach marek */

proc sql;
create table b.grupowanie_pojazdow as
select make, count(*) as liczba_pojazdow,
sum(invoice) as wartosc_pojazdow
from sashelp.cars
group by /*make*/ 1;

quit; /* gdy wychodzę ze składni sql */

proc sql;
create table b.grupowanie_pojazdow as
select make, count(*) as liczba_pojazdow,
sum(invoice) as wartosc_pojazdow
count(distinct type) as typy_pojazdow /* distinct - unikalny */
from sashelp.cars
group by /*make*/ 1;

/*3*/
/* case when */
/* Pobierz dane z sashelp.cars i w zależności od kolumny
invoice utwórz kolumnę kategoria_cenowa
('niska', 'srednia', 'wysoka') */

proc sql;
create table b.kategoria_cenowa as
select make, model, invoice,
    case when invoice <300000 then 'niska'
    when invoice <600000 then 'srednia'
    else 'wysoka'
end as kategoria_cenowa
from sashelp.cars;

/*4*/
/* Podaj najtańsze auto w tabeli,
zachowaj w niej kolumny make, model, invoice */

/*1 - za pomocą having */
proc sql;
create table b.najtansze as
select make, model, invoice,
from sashelp.cars
having invoice=min(invoice);

/*2 - za pomocą order by */
proc sql outobs=1; /*ile rekordów zwraca */
create table b.najtansze as
select make, model, invoice
from sashelp.cars
order by invoice;

/*3 - za pomocą where */
proc sql ;
create table b.najtansze as
select make, model, invoice
from sashelp.cars
where invoice = ( select min(invoice) from sashelp.cars ) ; /* kwerenda zagnieżdżona */

/*5*/
/* Stworzyc zestawienie w podziale na ORIGIN
Tabela ma zawierać kolumny ORIGIN, liczbę pojazdów, udział procentowy */

proc sql ;
create table b.origin as
select origin, count(*) as liczba_pojazdow
    (select count(*) from sashelp.cars) as total_cars,
    (calculated liczba_pojazdow)/ (calculated total_cars)
    as udzial_procentowy format percent8.2
from sashelp.cars
group by 1 ;

/* lub */

proc sql ;
create table b.origin as
select origin, count(*) as liczba_pojazdow
    (select count(*) from sashelp.cars) as total_cars,
    count(*)/ (select count(*) from sashelp.cars)
    as udzial_procentowy format percent8.2
from sashelp.cars
group by 1 ;

/*6*/
/* Na podstawie imion czytelników ustalić liczbę czytelników
kobiet i mężczyzn w każdym mieście oraz udział procentowy
Tabela ma zawierać 5 kolumn: miasto, l_kobiet, udział % kobiet,
l_mezczyzn, udział & mężczyzn */

proc sql ;
create table b.miasto_plec as
select adres,
sum(substr(CZYTELNIK_IMIE, length(CZYTELNIK_IMIE),1)='a')
    as liczba_kobiet,
sum(substr(CZYTELNIK_IMIE, length(CZYTELNIK_IMIE),1) NE 'a')
    as liczba_mezczyzn,
(calculated liczba_kobiet) / (calculated liczba_kobiet + calculated liczba_mezczyzn)
as udzial_pct_kobiet format percent8.2,
1 - (calculated udzial_pct_kobiet)
as udzial_pct_mezczyzn format percent8.2
from b.czytelnik
group by ADRES ;

/*7*/
/* Dla kazdego czytelnika podaj liczbę wypożyczonych ksiazek w kazdym kwartale 2007
Tabela ma miec 5 kolumn i 58 wierszy(czytelników)*/

proc sql ;
create table b.wypozyczenia_kwartaly as
select czytelnik_id,
    sum(qtr(DATA_WYPOZYCZENIA)=1) as kw1,
    sum(qtr(DATA_WYPOZYCZENIA)=2) as kw2,
    sum(qtr(DATA_WYPOZYCZENIA)=3) as kw3,
    sum(qtr(DATA_WYPOZYCZENIA)=4) as kw4
from b.wypozyczenia
where year(data_wypozyczenia)=2007
group by 1 ;

/*8*/
/* Ustal wypożyczone i nieodane książki na dzień 1 października 2007 roku */


proc sql;
create table b.stan_na_1_paz as
select *
from b.wypozyczenia
where data_wypozyczenia<'01OCT2007'd
and (data_oddania>='01OCT2007'd or data_oddania=.);

/*******************************/
/* 15/10/2018 */

/* 1 */
/* wyznacz 5 czytelików z największą
    liczbą nieoddanych ksiażek: podaj imie, nazwisko, adres
i liczbę nieoddanych ksiażek */

proc sort data=b.WYPOZYCZENIA;
by  czytelnik_id; run;

data b.nieoddane ;
set b.WYPOZYCZENIA ;
where data_oddania = . ;
by  czytelnik_id;
if first.czytelnik_id then licz_ks=0;
licz_ks +1;
if last.czytelnik_id;
keep czytelnik_id licz_ks;
run;

proc sort data=b.nieoddane;
by descending licz_ks; run;

data b.nieoddane_top5 ;
set b.nieoddane;
if _N_=5 then call symput('N5', licz_ks);
run;
data b.nieoddane_top5 ;
set b.nieoddane_top5 ;
where licz_ks>=&N5; /* & - macro parametr */
run;

/* zrobić łaczenie z tabelą czytelnik */

/******************************/
/* SAS SQL */
/******************************/
/* 2 */
proc sql;
create table b.auta as
select *
from sashelp.cars
where  make in ('Audi', 'BMW')  ;

/* 3 */
/* na podstawie tabeli cars utwórz kolumnę nowa_cena,
która stanowi 90% wartość z kolumny invoice,
wartości proszę zaokrąglić do 2 m-c po przecinku */
proc sql;
create table b.nowa_Cena as
select make, model, invoice,
ROUND(invoice*0.9,0.01) as nowa_cena format dollar12.2
from sashelp.cars ;

/* 4 */
/* zlicz ilość i wartosc pojazdów w ramach marek */
proc sql;
create table b.grupowanie_pojazdow as
select make, count(*) as liczba_pojazdow,
sum(invoice) as wartosc_pojazdow,
count(distinct type) as typy_pojazdow
from sashelp.cars
group by /*make*/ 1 ;

/*quit;*/ /* gdy wychodzę, ze składni SQL */

/* 5 */
/* case when*/
/* pobierz dane z sashelp.cars i w zalezności od kolumny
invoice utworz kolumnę kategoria_cenowa
('niska', 'średnia','wysoka') */

proc sql;
create table b.kategoria_cenowa as
select make, model, invoice,
        case when invoice<30000 then 'niska'
        when invoice<60000 then 'średnia'
        else 'wysoka'
        end as kategoria_cenowa
from sashelp.cars ;

/* 6*/
/* podaj najtansze auto w tabeli */
/* w tabeli zachowaj kolumny, make, model, invoice */

proc sql;
create table b.najtansze as
select make, model, invoice
from sashelp.cars
having invoice=min(invoice) ;
/*lub*/
proc sql outobs=1; /* outobs - numerator */
create table b.najtansze as
select make, model, invoice
from sashelp.cars
order by invoice ;
/* lub */
proc sql ;
create table b.najtansze as
select make, model, invoice
from sashelp.cars
where invoice= ( select min(invoice) from sashelp.cars ) ;
/* 7*/
/* stworzyć zestawienie w podziale na ORIGIN */
/* tabela ma zawierac kolumny ORIGIN,
liczbę pojazdów, udział procentowy */
proc  sql;
create table b.origin as
select origin, count(*) as liczba_pojazdow,
    (select count(*) from sashelp.cars) as total_cars ,
    (calculated liczba_pojazdow )/ (calculated total_cars)
    as udzial_procentowy format percent8.2
from sashelp.cars
group by 1 ;
/* lub */
proc  sql;
/*create table b.origin as*/
select origin, count(*) as liczba_pojazdow,
    (select count(*) from sashelp.cars) as total_cars ,
    count(*)/(select count(*) from sashelp.cars)
    as udzial_procentowy format percent8.2
from sashelp.cars
group by 1 ;
/* 8 */
/* na podstawie imion czytelników ustalić liczbę czytelników
kobiet i męzczyzn w kazdym mieście oraz udział procentowy */
/* tabela ma zawierac 5 kolumn: miasto, l_kobiet, udział % kobiet,
l_mezczyz, udział % mezczyz */
proc sql;
create table b.miasto_plec as
select adres,
sum(substr(CZYTELNIK_IMIE, length(CZYTELNIK_IMIE),1)='a')
            as liczba_kobiet,
sum(substr(CZYTELNIK_IMIE, length(CZYTELNIK_IMIE),1) NE 'a')
            as liczba_mezczyzn,
(calculated liczba_kobiet)/
(calculated liczba_kobiet + calculated liczba_mezczyzn)
            as udzial_pct_kobiet format percent8.2,
(1- calculated udzial_pct_kobiet )
            as udzial_pct_mezczyzn format percent8.2
from b.czytelnik
group by ADRES ;
/* 9 */
/* dla kazdego czytelnika podaj liczbę wypozyczonych
ksiazek w kazdym kwartale 2007 roku */
/* tabela ma mieć 5 kolumn i 58 wierszy(czytelników) */
proc sql;
create table b.wypozyczenia_kwartaly as
select czytelnik_id,
    sum(qtr(DATA_WYPOZYCZENIA)=1) as wyp_kw_1,
    sum(qtr(DATA_WYPOZYCZENIA)=2) as wyp_kw_2,
    sum(qtr(DATA_WYPOZYCZENIA)=3) as wyp_kw_3,
    sum(qtr(DATA_WYPOZYCZENIA)=4) as wyp_kw_4
from b.wypozyczenia
group by 1;
/* 10*/
/* ustal wypozyczone i nieoddane ksiazki
na dzien 1 paźdzernika 2007 roku */
proc sql;
create table b.stan_na_1_paz as
select *
from b.wypozyczenia
where DATA_WYPOZYCZENIA<'01OCT2007'd
and (DATA_ODDANIA>='01OCT2007'd or DATA_ODDANIA=. );

/*****************************/
/* 22.10.2018 */
/*****************************/

/* 1 */
/* Zlicz czytelników w podziale na płeć */

proc sql;
create table b.plec as
select case when
substr(CZYTELNIK_IMIE, length(CZYTELNIK_IMIE),1)='a' then 'K'
else 'M'
end as plec, count(*) as liczba_czytelnikow
from b.czytelnik
group by 1;

/* 2 */
/* Dla każdego czytelnika ustal datę ostatniego wypożyczenia
i zlicz liczbę wypożyczonych książek w okresie ostatnich 30 dni */

proc sql;
create table b.wypozyczenia_30 as
select CZYTELNIK_ID, count(*) as wypozyczenia_30
from(
select CZYTELNIK_ID, DATA_WYPOZYCZENIA, max(DATA_WYPOZYCZENIA) as MAX_DATA format ddmmyy10.
from b.wypozyczenia
group by CZYTELNIK_ID
having (max(DATA_WYPOZYCZENIA)-30) <= DATA_WYPOZYCZENIA <= (max(DATA_WYPOZYCZENIA))
group by 1;

/* 3 */
/* Dla każdego czytelnika podaj średni czas wypożyczenia książki,
w przypadku, gdy książka jest nieoddana wstaw datę 31/12/2007 */

proc sql;
create table b.czas_wypozyczenia as
select CZYTELNIK_ID,
ROUND(avg(coalesce(DATA_ODDANIA, '31DEC2007'd) - DATA_WYPOZYCZENIA), 0.1)
as avg_czas_wypozyczenia
from b.wypozyczenia
group by 1;

/* 4 */
/* Podaj 10 czytelników z najwyższą liczbą wypożyczeń */
/* W tabeli chcemy: IMIE, NAZWISKO, ADRES, liczba_wypozyczen */

proc sql outobs=10;
create table b.top_10 as
select c.CZYTELNIK_IMIE, c.CZYTELNIK_NAZWISKO, c.ADRES,
count(*) as liczba_wypozyczen,
sum( DATA_ODDANIA NE . ) as liczba_oddan
from b.wypozyczenia as w inner join b.czytelnik as c
on w.czytelnik_id=c.id
group by 1,2,3
order by 4 desc
;
/* 5 */
/* Utworzyć ranking najbardziej poczytnych tytułów
w tabeli podaj tytuł, autora i liczbe wypozyczen */

proc sql;
create table b.ranking_tytulow as
select TYTUL, AUTOR_NAZWISKO, count(*) as liczba_wypozyczen,
count(distinct KSIAZKA_SYGNATURA) as liczba_egzemplarzy
from b.KSIAZKA as a, b.WYPOZYCZENIA as w
where a.SYGNATURA=w.KSIAZKA_SYGNATURA
group by 1,2
order by 3 desc;

/* 6 */
/* Ustal czytelników, którzy wypożyczyli daną książkę więcej niż jeden raz */

proc sql;
create table b.wypozyczenia_ponowne as
select CZYTELNIK_ID, KSIAZKA_SYGNATURA, count(*) as wypozyczenia_ponowne
from b.wypozyczenia
group by 1, 2
having wypozyczenia_ponowne>1;

/* 7 */
/* Ustal staz czytelnika w latach i miesiacach
na podstawie daty zapisania do biblioteki */

proc sql;
create table b.staz as
select *,
intck('month', DATA_ZAPISU, today()) as staz_mcach,
intck('year', DATA_ZAPISU, today()) as staz_latach
from b.czytelnik;