options ls=80 nodate pageno=1 NOFMTERR; 

 *  Class24_11.sas    ;

  title3;
 
 * data L = "Family Economic Data.xls" ;

  Proc SQL;
    create table M as
    select   family_id,  count(income) as n_years_income
    from L
    group by family_id;
    quit;


 Proc print data=M (obs=5);
   

  Proc Freq data=M;
    tables n_years_income; 

 title3;

  Proc SQL;
    create table M1 as
    select distinct family_id, count(income) as n_years_income, 
    sum(income,-1.0*expenses) as net_income
    from L
    group by family_id;
    quit;

  Proc print data=M1 (obs=5);

  Proc SQL;
    create table M2 as
    select  *, count(income) as n_years_income
    from L
    group by family_id;
    quit;


 Proc print data=M2 (obs=15);

  run;
   
 Proc Transpose data=L out=wide prefix=income_;
   ID year;
   VAR income;
   BY family_id;

 proc print data=wide(obs=10);

  Proc SQL;
    create table M3 as
    select   cohort, count(income) as n_years_income
    from L
    group by cohort;
    quit;

    proc print data=m3;

  run; quit;

  ******************************;
  data a;
   input id color$ mass;
   cards;
   12 orange 3650
   13 blue  3877
   15 yellow 4103
   ;

 data b;
   input id mass pH;
   cards;
   13 11267 7.8
   14 3568 8.2
   15 4103 5.1
   ;

 Proc SQL;
    create table S as
    select * from  A(rename=(id=id_A mass=mass_A)), B(rename=(id=id_B mass=mass_B));

    proc print data=S;

    run;

 Proc SQL;
   create table G as
    select * from A(rename=(mass=mass_A)), B(rename=(mass=mass_B))
    where A.id  = B.id;

 Proc Print data=G;


 run;
****************************************************;
 *********  fuzzy merge, step by step **************;
 data weights;
   input id date :DATE9. kg;
   format date DATE10.;
   cards;
  33 10mar2009 78
  33 12apr2009 81
  33 15may2009 80
  34 02feb2009 65
  34 05jun2009 66
  35 21may2009 71
  35 08jun2009 71
  35 14aug2009 70
  ;

 data bp;
   input patient date :DATE9. dbp sbp;
   format date DATE10.;
   cards;
  33 15mar2009 72 112
  33 10apr2009 68 105
  33 30may2009 71 110
  34 07feb2009 55 95
  34 15jun2009 60 99
  35 21may2009 66 110
  35 06jun2009 68 105
  ;

 Proc print data=weights;
 Proc print data=BP;

 Proc SQL;
   create table G1 as
    select * 
    from weights(rename=(date=date_wt)), BP (rename=(date=date_BP))
    where weights.id  = BP.patient;

 proc print data=G1;

  Proc SQL;
   create table G2 as
    select * , abs(date_wt - date_BP) as interval 
    from weights(rename=(date=date_wt)), BP (rename=(date=date_BP))
    where weights.id  = BP.patient ;

 proc print data=G2;


  Proc SQL;
    create table G3 as
    select * , abs(date_wt - date_BP) as interval 
    from weights(rename=(date=date_wt)), BP (rename=(date=date_BP))
    where weights.id  = BP.patient and (calculated interval LE 7);

 proc print data=G3;

  Proc SQL;
    create table G4 as
    select * , abs(date_wt - date_BP) as interval 
    from weights(rename=(date=date_wt)), BP (rename=(date=date_BP patient=id))
    where weights.id  = BP.id and (calculated interval LE 7);

 proc print data=G4;

 run; quit;