Classic Data Management Problems Working Across Variables

* Working with data across variables ;

* 1. recoding data across variables ;
* 2. collapsing data across variables ;
* 3. look for a pattern across variables, ever happened ;
* 4. look for patterns, and note every time it happened ;

* read in income data for jan to dec ;
DATA faminc; 
  INPUT famid faminc1-faminc12 ;
CARDS; 
1 3281 3413 3114 2500 2700 3500 3114 3319 3514 1282 2434 2818 
2 4042 3084 3108 3150 3800 3100 1531 2914 3819 4124 4274 4471
3 6015 6123 6113 6100 6100 6200 6186 6132 3123 4231 6039 6215
;
RUN;

* 1a. recode variable, compute tax paid ;
* the manual way, hard with lots of months ;
DATA faminc1a;
  set faminc;

  taxinc1 = faminc1 * .10 ;
  taxinc2 = faminc2 * .10 ;
  taxinc3 = faminc3 * .10 ;
  taxinc4 = faminc4 * .10 ;
  taxinc5 = faminc5 * .10 ;
  taxinc6 = faminc6 * .10 ;
  taxinc7 = faminc7 * .10 ;
  taxinc8 = faminc8 * .10 ;
  taxinc9 = faminc9 * .10 ;
  taxinc10= faminc10 * .10 ;
  taxinc11= faminc11 * .10 ;
  taxinc12= faminc12 * .10 ;
RUN;

* 1b. when you have lots of months, an easier way would be ;
DATA faminc1b;
  set faminc ;

  ARRAY Afaminc(12) faminc1-faminc12 ;
  ARRAY Ataxinc(12) taxinc1-taxinc12 ;

  DO month = 1 to 12;
    Ataxinc(month) = Afaminc(month) * .10 ;
  END;
RUN;

* check results ;
PROC PRINT DATA=faminc1b;
  var faminc1-faminc12 taxinc1-taxinc12;
RUN;

* 2. collapsing across variables ;
* want to make quarterly incomes ;

* 2a. manual way, hard if you have dozens of months ;
DATA faminc2a;
  set faminc;

  incqtr1 = faminc1+faminc2+faminc3 ;
  incqtr2 = faminc4+faminc5+faminc6 ;
  incqtr3 = faminc7+faminc8+faminc9 ;
  incqtr4 = faminc10+faminc11+faminc12 ;
RUN;

* 2b. using array, easier if you have dozens of months ;
DATA faminc2b;
  set faminc ;

  ARRAY Afaminc(12) faminc1-faminc12 ;
  ARRAY Aincqtr(4)  incqtr1-incqtr4 ;

  DO qtr = 1 to 4 ;
    month3 = 3*qtr;
    Aincqtr(qtr) = Afaminc(month3-2) + Afaminc(month3-1) + Afaminc(month3) ;
  END;
RUN;

* check by looking at observations ;
PROC PRINT DATA=faminc2b;
  VAR faminc1-faminc12 incqtr1-incqtr4 ;
RUN;

PROC MEANS DATA=faminc2b N MEAN ;
  VAR faminc1-faminc12 incqtr1-incqtr4 ;
RUN;

* 3. looking for patterns across variables ;
* see if income ever was half of previous month ;
* using array, easier if you have dozens of months ;
DATA faminc3;
  set faminc ;

  ARRAY Afaminc(12) faminc1-faminc12 ;

  badinc = 0;
  DO month = 2 to 11 ;
    IF Afaminc(month) < ( Afaminc(month-1) / 2) THEN badinc = 1;
  END;
RUN;

PROC PRINT DATA=faminc3;
  VAR famid badinc faminc1-faminc12;
RUN;


* 4. looking for patterns across variables ;
* see if income ever was half of previous month, and store which ;
* months it occurred ;
DATA faminc4;
  set faminc ;

  ARRAY Afaminc(12) faminc1-faminc12 ;
  ARRAY Abadinc(2:12) badinc2-badinc12 ;

  DO month = 2 to 12 ;
    IF Afaminc(month) < ( Afaminc(month-1) / 2) THEN Abadinc(month) = 1;
      ELSE Abadinc(month) = 0;
  END;
RUN;

PROC PRINT DATA=faminc4;
 VAR famid faminc1-faminc12 ;
run;

PROC PRINT DATA=faminc4;
 VAR famid badinc2-badinc12 ;
run;