home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #26 / NN_1992_26.iso / spool / bit / listserv / sasl / 5031 < prev    next >
Encoding:
Internet Message Format  |  1992-11-12  |  4.8 KB
  1. Path: sparky!uunet!utcsri!torn!nott!dgbt!netfs!ub!zaphod.mps.ohio-state.edu!darwin.sura.net!europa.asd.contel.com!paladin.american.edu!auvm!NIHCU.BITNET!HIS
  2. From: HIS@NIHCU.BITNET (Howard Schreier)
  3. Newsgroups: bit.listserv.sas-l
  4. Subject: Tricky Lookback Problem
  5. Message-ID: <SAS-L%92111210220231@UGA.CC.UGA.EDU>
  6. Date: 12 Nov 92 15:21:21 GMT
  7. Sender: "SAS(r) Discussion" <SAS-L@UGA.BITNET>
  8. Reply-To: Howard Schreier <HIS@NIHCU.BITNET>
  9. Lines: 121
  10. Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU
  11.  
  12. CONTENT:  Response
  13. SUMMARY:  DATA step can be more efficient than SQL
  14.  
  15. > From:         GFX@PSUVM.PSU.EDU
  16. >
  17. > The dataset is structured like this:
  18. >
  19. >         OBS  Date       x1 x2 ... xn  N_12Mo
  20. >          1   04/12/82   1  0      1
  21. >          2   04/21/82   1  1      0
  22. >          3   04/28/82   0  0      1
  23. >          .
  24. >          .
  25. >          K
  26. >
  27. > Variable N_12Mo should indicate the number of observations
  28. > that are less than a year old. In other words, how many
  29. > lines above observation K are less than a year old. We used
  30. > the LOOKUP procedure in Microsoft Excel. How could it be
  31. > done in SAS?
  32. >
  33. > Thanks,
  34. > Stephane
  35.  
  36. I've been thinking about this one off and on for a few days.
  37. I  assume  that there is at most one observation per day and
  38. that the file is  sorted,  so  that  "above"  translates  to
  39. *strictly*  earlier.  Also, I agree with Bob Snyder's advice
  40. on  using  the  SAS  convention   for   representing   dates
  41. internally.  So, here is my test file:
  42.  
  43.    data test; input date mmddyy8. x1-x4; cards;
  44.    04/11/90 0 0 0 1
  45.    04/12/90 0 0 1 0
  46.    04/13/90 0 0 1 1
  47.    04/11/91 0 1 0 0
  48.    04/12/91 0 1 0 1
  49.    04/13/91 0 1 1 0
  50.    04/11/92 0 1 1 1
  51.    04/12/92 1 0 0 0
  52.    04/13/92 1 0 0 1
  53.    04/14/92 1 0 1 0
  54.    04/15/92 1 0 1 1
  55.    ;
  56.  
  57. A solution using SQL is fairly natural:
  58.  
  59.    proc sql;
  60.    create table sresult as
  61.     select distinct test.date format=mmddyy8.,
  62.                     test.x1,test.x2,test.x3,test.x4,
  63.                     n(joindate.date) as n_12mo
  64.      from test   left join   test as joindate
  65.      on mdy(month(test.date),
  66.             day  (test.date),
  67.             year (test.date) - 1) < joindate.date < test.date
  68.      group by test.date
  69.      order by test.date
  70.     ;
  71.  
  72. The essential technique is to JOIN the data set with  itself
  73. (LEFT  JOIN  is used, to generate a row even when the answer
  74. is  zero).   The  ON  condition  implements   the   one-year
  75. lookback;  by using MDY instead of just subtracting 365, the
  76. query gives correct results even when a leap day (Feb.   29)
  77. intervenes.
  78.  
  79. So, SQL can do the job, but...  (and this is a big ol' "but"
  80. :-)).    The  JOIN  required  is  *not*  an  "equijoin"  and
  81. therefore cannot be optimized very  much;  in  other  words,
  82. PROC SQL will be forced to use brute force in evaluating it.
  83. Moreover, I suspect that the  real  data  set  here  may  be
  84. fairly large.  If it has just 5K observations, the JOIN will
  85. entail forming 5Kx5K=25M pairings before  the  ON  condition
  86. whittles  it  down  and the DISTINCT option purges duplicate
  87. rows.
  88.  
  89. So, unless the data set is quite  small,  the  SQL  solution
  90. will  be  very  inefficient,  and  a  different  approach is
  91. needed.  This is a situation where the  flexibility  of  the
  92. DATA step really helps:
  93.  
  94.    data dresult;
  95.    set test;
  96.    year_ago = mdy(month(date),day(date),year(date)-1);
  97.    if backwhen<=year_ago then do until (backwhen>year_ago);
  98.       set test(keep=date rename=(date=backwhen));
  99.       count + 1;
  100.       end;
  101.    n_12mo = _n_ - count;
  102.    format date year_ago backwhen mmddyy8.;
  103.  
  104. The  presence  of  two  SET  statements  makes  this  pretty
  105. unusual.   It  takes advantage of the fact that multiple SET
  106. statements  operating  on  the  same   data   set   maintain
  107. independent  pointers.   The first SET works conventionally,
  108. advancing one observation per  iteration.   The  second  one
  109. advances as necessary to remain a year behind.
  110.  
  111. I get the same results using either SQL or the DATA step:
  112.  
  113.    OBS        DATE    X1    X2    X3    X4    N_12MO
  114.  
  115.      1    04/11/90     0     0     0     1       0
  116.      2    04/12/90     0     0     1     0       1
  117.      3    04/13/90     0     0     1     1       2
  118.      4    04/11/91     0     1     0     0       2
  119.      5    04/12/91     0     1     0     1       2
  120.      6    04/13/91     0     1     1     0       2
  121.      7    04/11/92     0     1     1     1       2
  122.      8    04/12/92     1     0     0     0       2
  123.      9    04/13/92     1     0     0     1       2
  124.     10    04/14/92     1     0     1     0       3
  125.     11    04/15/92     1     0     1     1       4
  126.  
  127. /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
  128. \   Howard Schreier, U.S. Dept. of Commerce, Washington    /
  129. /                     MVS 5.18 & 6.07                      \
  130. \   Voice: (202) 377-4180        BITNET: HIS@NIHCU         /
  131. /   Fax:   (202) 377-4614      INTERNET: HIS@CU.NIH.GOV    \
  132. \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/
  133.