Help: How to get this output (complicated case)

shiyiming

I have a dataset like this:
id    startdate    enddate    day_of_week   binary_value    Type           sum_working
AA   01AUG08    31DEC10        2                    101             monthly           200

binary_value  101 means every first and third week of that month.
day of week=2 means every monday

What I want for the output is every first and third monday for each month from 01AUG08 to 31DEC10, like this

id    outputdate    day_of_week   sum_working
AA    04AUG08           2                   200
AA    18AUG08           2                   200
AA    01SEP08            2                   200
AA    15SEP08            2                   200
...

For different row, the day of the week and binary value could be different (for example day_of_week=5 and binary_value=1010, which means every second and forth Thursday of the month) .

This is what I did for weekly, seems easier than monthly.

data setp.blocksoutput;
set setp.blocks;
format startdate stopdate yymmdd8.;
do while (startdate<=stopdate);
weekday=weekday(startdate);
if weekday=day_of_week then output;
startdate+1;
end;
run;

I appreciate anyone's help.

Qiong

[code:sdq6rx49]data v;
input id $2. startdate $  stopdate  $  day_of_week  binary_value $  Type $ sum_working;
cards;
AA 01AUG08 31DEC08 2 101 monthly 200
BB 15SEP09 31DEC09 5 1010 monthly 250
;
run;

data v_o ;
set v;
/*set type*/
if  binary_value ='101'  then  flag=0 ;
if  binary_value ='1010'  then  flag=1;

/*Set month beginning date*/
startmonth=intnx('month',input(startdate,date7.),0);
stopmonth=intnx('month', input(stopdate,date7.),0);

do while (startmonth<=stopmonth);
/*get the first (second) in one month*/
        if weekday(startmonth)^= day_of_week then;
        do until(weekday(startmonth)=day_of_week);
        startmonth+1;
        end;
        startmonth=startmonth+flag*7;
        start= put(startmonth,date7.) ;
        if startmonth>=input(startdate,date7.) and startmonth<= input(stopdate,date7.) then output;

/*get the third(forth) in one month*/
        startmonth=intnx('day',startmonth,14);
        start= put(startmonth,date7.) ;
        if startmonth>=input(startdate,date7.) and  startmonth<= input(stopdate,date7.)
     and  intck('month',startmonth-14,startmonth)=0 then output;
        startmonth=intnx('month', startmonth,1);
end;  
drop startmonth stopmonth flag;
run;  [/code:sdq6rx49]

shiyiming

死猪头

shiyiming

谢谢死猪头的程序!
ifn()比写if语句看着视觉上更清楚了;
array与band()一起处理2进制数的方法我还是头次学到.

shiyiming

Thanks everyone. It works.

shiyiming

Sorry I found another situation. Last time is monthly. This time is multi-weekly. The data look like this:
id startdate   enddate    day_of_week binary_value  Type        pattern   sum_working
AA 01AUG08  31DEC10      5                   11011        multiweely     6        200
AA 01AUG08  31DEC10      2                   101101      multiweely     6        200

pattern=6 means six weeks in the cycle. It could be 1 2 3 4 6 8.
day of week=2 means every monday   day of week=5 means every Thursday

For the first row, what I want to output is from 01Aug08 to six weeks later, output the first, second, fourth and fifth Thursday. Similarly for the second row.

I appreciate your help.

shiyiming

请问goldfish: pattern存在于数据集中,这是否意味着在应用中还有取其他值的可能呢?比如pattern=5时,值为1 2 4 7 8?

shiyiming

The value of pattern represents the number of weeks in the cycle when it is multi-week type. pattern=6 means six-week cycle. For example, when pattern=6 and binary value=011011 and day of week=2, it means we need the output for first, second, fourth and fifth Monday of that six-week cycle.

shiyiming

I got. It is simple based on the code above.
data setp.v_k ;
   array bases[5] _temporary_ (1 2 4 8 16);
   set setp.v;
   startweek = intnx('week', startdate, 0);
   do while(startweek le stopdate);
      do _n_=1 to dim(bases);
         if band(bases[_n_], binary_value) then do;
            start = ifn(weekday(startweek)=day_of_week,
                     intnx(cats('week.', day_of_week), startweek, _n_-1),
                     intnx(cats('week.', day_of_week), startweek, _n_)
                  );
            if startdate le start le stopdate then output;
         end;
      end;
      startweek=intnx('week', startweek, pattern);
   end;
   drop startweek;
   format start yymmdd10.;
run;  

Thanks guys.

shiyiming

[b:3iwnfgdu]按照月计算的代码[code:3iwnfgdu]data have;
input id $2. startdate:date7.  stopdate:date7.  day_of_week  binary_value $  Type $ sum_working;
cards;
AA 01AUG08 31DEC08 2 101 monthly 200
BB 15SEP09 31DEC09 5 1010 monthly 250
;
run;

data want(keep=id day_of_week month_week sum_working);
set have;
b=find(binary_value,'1');
a=length(compress(binary_value))- b +1;
do while (b ^=0);
startmonth=intnx('month',startdate,0);
n=0;
do while(startmonth < stopdate );
month_week=ifn(weekday(startmonth)=day_of_week,
                     intnx(cats('week.', day_of_week), startmonth, a-1),
                     intnx(cats('week.', day_of_week), startmonth, a));
format month_week mmddyy10.;
if strartmonth le month_week le stopdate then output;
substr(binary_value,b,1)='0';
n+1;
startmonth=intnx('month',startdate,n);
end;
b=find(binary_value,'1');
a=length(compress(binary_value))- b +1;
end;
run;
[/code:3iwnfgdu][/b:3iwnfgdu]