Data structure
in [SAS]
|
Prev: Change from Baseline .
Next: do while
From: Al on 16 Jun 2010 16:59 On Jun 16, 3:39 pm, Ya <huang8...(a)gmail.com> wrote: > On Jun 16, 11:39 am, Al <ali6...(a)gmail.com> wrote: > > > > > > > On Jun 16, 1:29 pm, Reeza <fkhurs...(a)hotmail.com> wrote: > > > > The following doesn't make sense to me. > > > > > 1<Day_AE<=23 must fall under Day 23 column and x must be assinged > > > > under Day 23 column > > > > and should be going across the rest of the columns if daysp is > > > > missing > > > > and if daysp is not missing x must be placed under the range of > > > > columns .. > > > > Here's code that does some of what you'd like. > > > Cheers, > > > Reeza > > > > data lab; > > > input pat lab $ day; > > > cards; > > > 1 BUN 1 > > > 1 BUN 23 > > > 1 BUN 30 > > > 1 BUN 46 > > > 1 BUN 56 > > > > run; > > > data ae; > > > input pat seq day_ae daysp; > > > cards; > > > 1 1 19 25 > > > 1 2 29 . > > > 1 3 40 45 > > > 1 4 20 29 > > > 1 5 50 52 > > > run; > > > > proc sql; > > > create table temp1 as > > > select a.*, b.day > > > from ae a > > > cross join lab b > > > order by pat, seq, day; > > > quit; > > > > data temp2; > > > set temp1; > > > by seq; > > > if first.seq and day_ae<= day then category="X"; > > > if day_ae <= day and day_ae > lag1(day) then category="X"; > > > if day_ae <= day and daysp=. then category="X"; > > > run; > > > > proc transpose data=temp2(drop= day_ae daysp) prefix=day_ out=output; > > > by pat seq; > > > variable category; > > > id day; > > > run; > > > Thanks but i am also looking for an X if daysp exsits > > for example > > for pat = 1 ,seq = 1 ,daysp = 25 there should be an x under day 30 > > column and should stop propagating then on .. > > hope i am clear .. please see the desired output in the earlier post > > > Thanks for your time > > Al- Hide quoted text - > > > - Show quoted text - > > A little modification to my code 3 days ago will work for the new > scenario: > > data ae; > set ae (rename=(day_ae=dayst)); > if daysp=. then daysp=58; > do day_ae=dayst to daysp; > output; > end; > keep pat seq day_ae; > run; > > proc sort data=ae; > by pat descending day_ae; > run; > > data lab; > set lab; > day_ae=day; > run; > > proc sort data=lab; > by pat descending day_ae; > run; > > data labae; > merge lab ae(in=b_); > retain labday; > by pat descending day_ae; > if first.pat then labday=.; > if ^missing(day) then labday=day; > else day=labday; > ae='x'; > if b_; > run; > > proc sort data=labae nodupkey; > by pat seq day; > run; > > proc transpose data=labae out=tran (drop=_name_) prefix=Day; > by pat seq; > var ae; > id labday; > run; > > proc print; > run; > > pat seq Day23 Day30 Day46 Day56 > > 1 1 x x > 1 2 x x x > 1 3 x > 1 4 x x > 1 5 x > > BTW, > > seq=3 should only fall in the day 46 bucket, since both 40-45 < 46.- Hide quoted text - > > - Show quoted text - Thanks Ya for your valuable time if i have mutiple pat value say (1,2,3 ...n )..i cannot use if daysp=. then daysp=58; because this may be different for different values of pat so , how did you chose this number . is it like the maximum of all the values in day.one option is to have a macro varible in place of 58 .. am i correct?? Thanks in advance Al
From: Ya on 16 Jun 2010 17:09 On Jun 16, 1:59 pm, Al <ali6...(a)gmail.com> wrote: > On Jun 16, 3:39 pm, Ya <huang8...(a)gmail.com> wrote: > > > > > > > On Jun 16, 11:39 am, Al <ali6...(a)gmail.com> wrote: > > > > On Jun 16, 1:29 pm, Reeza <fkhurs...(a)hotmail.com> wrote: > > > > > The following doesn't make sense to me. > > > > > > 1<Day_AE<=23 must fall under Day 23 column and x must be assinged > > > > > under Day 23 column > > > > > and should be going across the rest of the columns if daysp is > > > > > missing > > > > > and if daysp is not missing x must be placed under the range of > > > > > columns .. > > > > > Here's code that does some of what you'd like. > > > > Cheers, > > > > Reeza > > > > > data lab; > > > > input pat lab $ day; > > > > cards; > > > > 1 BUN 1 > > > > 1 BUN 23 > > > > 1 BUN 30 > > > > 1 BUN 46 > > > > 1 BUN 56 > > > > > run; > > > > data ae; > > > > input pat seq day_ae daysp; > > > > cards; > > > > 1 1 19 25 > > > > 1 2 29 . > > > > 1 3 40 45 > > > > 1 4 20 29 > > > > 1 5 50 52 > > > > run; > > > > > proc sql; > > > > create table temp1 as > > > > select a.*, b.day > > > > from ae a > > > > cross join lab b > > > > order by pat, seq, day; > > > > quit; > > > > > data temp2; > > > > set temp1; > > > > by seq; > > > > if first.seq and day_ae<= day then category="X"; > > > > if day_ae <= day and day_ae > lag1(day) then category="X"; > > > > if day_ae <= day and daysp=. then category="X"; > > > > run; > > > > > proc transpose data=temp2(drop= day_ae daysp) prefix=day_ out=output; > > > > by pat seq; > > > > variable category; > > > > id day; > > > > run; > > > > Thanks but i am also looking for an X if daysp exsits > > > for example > > > for pat = 1 ,seq = 1 ,daysp = 25 there should be an x under day 30 > > > column and should stop propagating then on .. > > > hope i am clear .. please see the desired output in the earlier post > > > > Thanks for your time > > > Al- Hide quoted text - > > > > - Show quoted text - > > > A little modification to my code 3 days ago will work for the new > > scenario: > > > data ae; > > set ae (rename=(day_ae=dayst)); > > if daysp=. then daysp=58; > > do day_ae=dayst to daysp; > > output; > > end; > > keep pat seq day_ae; > > run; > > > proc sort data=ae; > > by pat descending day_ae; > > run; > > > data lab; > > set lab; > > day_ae=day; > > run; > > > proc sort data=lab; > > by pat descending day_ae; > > run; > > > data labae; > > merge lab ae(in=b_); > > retain labday; > > by pat descending day_ae; > > if first.pat then labday=.; > > if ^missing(day) then labday=day; > > else day=labday; > > ae='x'; > > if b_; > > run; > > > proc sort data=labae nodupkey; > > by pat seq day; > > run; > > > proc transpose data=labae out=tran (drop=_name_) prefix=Day; > > by pat seq; > > var ae; > > id labday; > > run; > > > proc print; > > run; > > > pat seq Day23 Day30 Day46 Day56 > > > 1 1 x x > > 1 2 x x x > > 1 3 x > > 1 4 x x > > 1 5 x > > > BTW, > > > seq=3 should only fall in the day 46 bucket, since both 40-45 < 46.- Hide quoted text - > > > - Show quoted text - > > Thanks Ya for your valuable time > if i have mutiple pat value say (1,2,3 ...n )..i cannot use > if daysp=. then daysp=58; because this may be different for different > values of pat > so , how did you chose this number . is it like the maximum of all the > values in day.one option is to > have a macro varible in place of 58 .. am i correct?? > > Thanks in advance > Al- Hide quoted text - > > - Show quoted text - Yes, since your data structure implies that all possible Lab day should have it's own column, you can get the max lab day + 1 into a macro var.
From: Ya on 16 Jun 2010 19:12
On Jun 16, 1:59 pm, Al <ali6...(a)gmail.com> wrote: > On Jun 16, 3:39 pm, Ya <huang8...(a)gmail.com> wrote: > > > > > > > On Jun 16, 11:39 am, Al <ali6...(a)gmail.com> wrote: > > > > On Jun 16, 1:29 pm, Reeza <fkhurs...(a)hotmail.com> wrote: > > > > > The following doesn't make sense to me. > > > > > > 1<Day_AE<=23 must fall under Day 23 column and x must be assinged > > > > > under Day 23 column > > > > > and should be going across the rest of the columns if daysp is > > > > > missing > > > > > and if daysp is not missing x must be placed under the range of > > > > > columns .. > > > > > Here's code that does some of what you'd like. > > > > Cheers, > > > > Reeza > > > > > data lab; > > > > input pat lab $ day; > > > > cards; > > > > 1 BUN 1 > > > > 1 BUN 23 > > > > 1 BUN 30 > > > > 1 BUN 46 > > > > 1 BUN 56 > > > > > run; > > > > data ae; > > > > input pat seq day_ae daysp; > > > > cards; > > > > 1 1 19 25 > > > > 1 2 29 . > > > > 1 3 40 45 > > > > 1 4 20 29 > > > > 1 5 50 52 > > > > run; > > > > > proc sql; > > > > create table temp1 as > > > > select a.*, b.day > > > > from ae a > > > > cross join lab b > > > > order by pat, seq, day; > > > > quit; > > > > > data temp2; > > > > set temp1; > > > > by seq; > > > > if first.seq and day_ae<= day then category="X"; > > > > if day_ae <= day and day_ae > lag1(day) then category="X"; > > > > if day_ae <= day and daysp=. then category="X"; > > > > run; > > > > > proc transpose data=temp2(drop= day_ae daysp) prefix=day_ out=output; > > > > by pat seq; > > > > variable category; > > > > id day; > > > > run; > > > > Thanks but i am also looking for an X if daysp exsits > > > for example > > > for pat = 1 ,seq = 1 ,daysp = 25 there should be an x under day 30 > > > column and should stop propagating then on .. > > > hope i am clear .. please see the desired output in the earlier post > > > > Thanks for your time > > > Al- Hide quoted text - > > > > - Show quoted text - > > > A little modification to my code 3 days ago will work for the new > > scenario: > > > data ae; > > set ae (rename=(day_ae=dayst)); > > if daysp=. then daysp=58; > > do day_ae=dayst to daysp; > > output; > > end; > > keep pat seq day_ae; > > run; > > > proc sort data=ae; > > by pat descending day_ae; > > run; > > > data lab; > > set lab; > > day_ae=day; > > run; > > > proc sort data=lab; > > by pat descending day_ae; > > run; > > > data labae; > > merge lab ae(in=b_); > > retain labday; > > by pat descending day_ae; > > if first.pat then labday=.; > > if ^missing(day) then labday=day; > > else day=labday; > > ae='x'; > > if b_; > > run; > > > proc sort data=labae nodupkey; > > by pat seq day; > > run; > > > proc transpose data=labae out=tran (drop=_name_) prefix=Day; > > by pat seq; > > var ae; > > id labday; > > run; > > > proc print; > > run; > > > pat seq Day23 Day30 Day46 Day56 > > > 1 1 x x > > 1 2 x x x > > 1 3 x > > 1 4 x x > > 1 5 x > > > BTW, > > > seq=3 should only fall in the day 46 bucket, since both 40-45 < 46.- Hide quoted text - > > > - Show quoted text - > > Thanks Ya for your valuable time > if i have mutiple pat value say (1,2,3 ...n )..i cannot use > if daysp=. then daysp=58; because this may be different for different > values of pat > so , how did you chose this number . is it like the maximum of all the > values in day.one option is to > have a macro varible in place of 58 .. am i correct?? > > Thanks in advance > Al- Hide quoted text - > > - Show quoted text - Here is a array based solution, should be more efficient: data lab; input pat lab $ day; cards; 1 BUN 1 1 BUN 23 1 BUN 30 1 BUN 46 1 BUN 56 2 BUN 1 2 BUN 23 2 BUN 30 2 BUN 46 2 BUN 76 run; data ae; input pat seq day_ae daysp; cards; 1 1 19 25 1 2 29 . 1 3 20 25 1 4 20 29 1 5 30 35 2 1 19 25 2 2 29 . 2 3 40 45 2 4 20 29 2 5 50 70 run; proc sql; select distinct 'day'||put(day,best.-l) into : daylst separated by ' ' from lab order by day ; data ae1; set ae; if missing(daysp) then daysp=80; array lbd(*) $ &daylst; do k=1 to dim(lbd); lbd(k)=''; end; do i=1 to dim(lbd)-1; do aed=day_ae to daysp; dl=input(substr(vname(lbd(i)),4),best.); du=input(substr(vname(lbd(i+1)),4),best.); if dl < aed <= du then lbd(i+1)='*'; end; end; keep pat seq &daylst; run; proc print; run; pat seq day1 day23 day30 day46 day56 day76 1 1 * * 1 2 * * * * 1 3 * * 1 4 * * 1 5 * * 2 1 * * 2 2 * * * * 2 3 * 2 4 * * 2 5 * * HTH Ya |