libname b "C:\Users\Asia\Desktop\sas";
 
/*zadanie 1*/
proc sort data=b.customer out=b.zad1;
by miasto;
run;
 
data b.zad1;
set b.zad1;
by miasto;
flaga = (wiek>=18);
if first.miasto then liczba_klientow=0;
liczba_klientow+flaga;
if last.miasto then output;
keep miasto liczba_klientow;
run;
 


/*zadanie 2*/
proc sql;
create table b.zad2 as
select our_party_no, count(polaczenia) as liczba_pol, sum(polaczenia) as sum_pol, round((calculated suma_pol)/(calculated liczba_pol)) as avg_pol
from b.call
where call_type=1 and month(data_polaczenia)=2
group by our_party_no
having liczba_pol>400;
quit;
 

/*zadanie 3 sql*/
proc sql;
create table b.zad3 as
select c.phone_no, d.city, sum(polaczenia) as liczba_pol, round(sum(cost),0.01) as cost_pol
from b.call as c inner join b.customer as d on c.phone_no=d.our_party_no
where upcase(city)="WŁOSZCZOWA" and call_type=1
group by phone_no;
quit;


/*zadanie 3 4gl*/
proc sort data=b.customer out=b.customer_sort;
by phone_no;
run;
 
proc sort data=b.call out=b.call_sort;
by our_party_no;
run;
 
data b.zad3;
merge b.call_sort (in=a where=(call_type=1))
     b.customer_sort(in=b rename=(our_party_no=phone_no) where=(upcase(miasto)="WŁOSZCZOWA"));
by phone_no;
if a=b;
if first.phone_no then do;
     liczba_pol=0;
     cost_pol=0;
end;
liczba_pol+polaczenia;
cost_pol+cost;
if last.phone_no then output;
keep phone_no city liczba_pol cost_pol;
run;

 
/*zadanie 4*/
%macro klienci(miasto=,wiek=,typ=);
proc sql;
create table b.zad4_&miasto._&wiek._&typ. as
select customer_id, phone_no, age, city, type
from b.customer
where upcase(miasto)=upcase("&miasto.") and age>=&wiek. and upcase(type)=upcase("&typ.");
quit;
%mend klienci;
 
%klienci(miasto=Gdynia,wiek=18,typ=D);

%macro klienci(miasto= ,wiek= , typ= );
Proc sql;
Create table b.zad4_&miasto._&wiek._&typ as
Select customer_id, phone_no, age, city, type
From b.customer
Where upcase(city)=”&miasto.” AND  age >=&wiek. and  type = &typ.;
Quite;
%Mend klienci;


%klienci(miasto =’GDYNIA”, wiek=18, typ=”D”);