标题: Help: How to get this output (complicated case) [打印本页] 作者: shiyiming 时间: 2009-7-14 04:29 标题: Help: How to get this output (complicated case) 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 时间: 2009-7-14 11:10 标题: Re: Help: How to get this output (complicated case) [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;
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 时间: 2009-7-14 13:20 标题: Re: Help: How to get this output (complicated case) 死猪头作者: shiyiming 时间: 2009-7-14 14:35 标题: Re: Help: How to get this output (complicated case) 谢谢死猪头的程序!
ifn()比写if语句看着视觉上更清楚了;
array与band()一起处理2进制数的方法我还是头次学到.作者: shiyiming 时间: 2009-7-14 23:40 标题: Re: Help: How to get this output (complicated case) Thanks everyone. It works.作者: shiyiming 时间: 2009-7-15 00:04 标题: Re: Help: How to get this output (complicated case) 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 时间: 2009-7-15 10:34 标题: Re: Help: How to get this output (complicated case) 请问goldfish: pattern存在于数据集中,这是否意味着在应用中还有取其他值的可能呢?比如pattern=5时,值为1 2 4 7 8?作者: shiyiming 时间: 2009-7-15 21:02 标题: Re: Help: How to get this output (complicated case) 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 时间: 2009-7-15 21:42 标题: Re: Help: How to get this output (complicated case) 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 时间: 2009-8-10 12:15 标题: Re: Help: How to get this output (complicated case) [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]