Facebook
From Anorexic Penguin, 1 Year ago, written in Plain Text.
Embed
Download Paste or View Raw
Hits: 153
  1. libname b "C:\Users\Asia\Desktop\sas";
  2.  
  3. /*zadanie 1*/
  4. proc sort data=b.customer out=b.zad1;
  5. by miasto;
  6. run;
  7.  
  8. data b.zad1;
  9. set b.zad1;
  10. by miasto;
  11. flaga = (wiek>=18);
  12. if first.miasto then liczba_klientow=0;
  13. liczba_klientow+flaga;
  14. if last.miasto then output;
  15. keep miasto liczba_klientow;
  16. run;
  17.  
  18.  
  19.  
  20. /*zadanie 2*/
  21. proc sql;
  22. create table b.zad2 as
  23. select our_party_no, count(polaczenia) as liczba_pol, sum(polaczenia) as sum_pol, round((calculated suma_pol)/(calculated liczba_pol)) as avg_pol
  24. from b.call
  25. where call_type=1 and month(data_polaczenia)=2
  26. group by our_party_no
  27. having liczba_pol>400;
  28. quit;
  29.  
  30.  
  31. /*zadanie 3 sql*/
  32. proc sql;
  33. create table b.zad3 as
  34. select c.phone_no, d.city, sum(polaczenia) as liczba_pol, round(sum(cost),0.01) as cost_pol
  35. from b.call as c inner join b.customer as d on c.phone_no=d.our_party_no
  36. where upcase(city)="WŁOSZCZOWA" and call_type=1
  37. group by phone_no;
  38. quit;
  39.  
  40.  
  41. /*zadanie 3 4gl*/
  42. proc sort data=b.customer out=b.customer_sort;
  43. by phone_no;
  44. run;
  45.  
  46. proc sort data=b.call out=b.call_sort;
  47. by our_party_no;
  48. run;
  49.  
  50. data b.zad3;
  51. merge b.call_sort (in=a where=(call_type=1))
  52.      b.customer_sort(in=b rename=(our_party_no=phone_no) where=(upcase(miasto)="WŁOSZCZOWA"));
  53. by phone_no;
  54. if a=b;
  55. if first.phone_no then do;
  56.      liczba_pol=0;
  57.      cost_pol=0;
  58. end;
  59. liczba_pol+polaczenia;
  60. cost_pol+cost;
  61. if last.phone_no then output;
  62. keep phone_no city liczba_pol cost_pol;
  63. run;
  64.  
  65.  
  66. /*zadanie 4*/
  67. %macro klienci(miasto=,wiek=,typ=);
  68. proc sql;
  69. create table b.zad4_&miasto._&wiek._&typ. as
  70. select customer_id, phone_no, age, city, type
  71. from b.customer
  72. where upcase(miasto)=upcase("&miasto.") and age>=&wiek. and upcase(type)=upcase("&typ.");
  73. quit;
  74. %mend klienci;
  75.  
  76. %klienci(miasto=Gdynia,wiek=18,typ=D);
  77.  
  78. %macro klienci(miasto= ,wiek= , typ= );
  79. Proc sql;
  80. Create table b.zad4_&miasto._&wiek._&typ as
  81. Select customer_id, phone_no, age, city, type
  82. From b.customer
  83. Where upcase(city)=”&miasto.” AND  age >=&wiek. and  type = &typ.;
  84. Quite;
  85. %Mend klienci;
  86.  
  87.  
  88. %klienci(miasto =’GDYNIA”, wiek=18, typ=”D”);
  89.