Prev: A Complex marging Issue - Please be kind enough to take a look at
Next: how to automatically remove the first two rows in the excel file when using "View in Excel" in sas
From: Tom Smith on 9 Mar 2010 15:57
can anyone would be kind enough to look at this problem? I am not capable
to do it in a short cut way. Please help me out
From: Ya Huang on 9 Mar 2010 16:31
Try this:

proc sort data=a;
by sid1a AEVSTT1D;
run;

proc sort data=b out=b (rename=(SMDDOS1D=AEVSTT1D));
by sid1a SMDDOS1D;
run;

data ab;
merge a(in=a_) b(in=b_);
retain ct;
by sid1a AEVSTT1D;
if ^missing(cycle) then ct=cycle; else cycle=ct;
if a_;
drop ct;
run;

proc print;
run;

Obs SID1A AEVSTT1D AEVEND1D trt CYCLE

1 0012_000 17NOV2006 23NOV2006 B 1
2 0012_000 03DEC2006 19DEC2006 B 2
3 0012_000 21DEC2006 26DEC2006 B 3
4 0012_000 27DEC2006 19JAN2007 B 3
5 0012_000 27DEC2007 29JAN2008 B 3
6 0150_000 11APR2007 11APR2007 A 1
7 0150_000 18APR2007 20APR2007 A 1
8 0150_000 02MAY2007 04MAY2007 A 2
9 0150_000 05MAY2007 07MAY2007 A 2
10 0150_000 08MAY2007 08MAY2007 A 2
11 0150_000 09MAY2007 10MAY2007 A 2
12 0150_000 11MAY2007 16MAY2007 A 2
13 0150_000 24MAY2007 24MAY2007 A 3
14 0150_000 26MAY2007 26MAY2007 A 3
15 0150_000 27MAY2007 27MAY2007 A 3
16 0150_000 28MAY2007 28MAY2007 A 3
17 0150_000 29MAY2007 29MAY2007 A 3
18 0150_000 30MAY2007 31MAY2007 A 3
19 0150_000 30AUG2008 30MAY2008 A 3


According to my calculation, the second record for 0012_000, should
get cycle of 2, yours below want 3. I believe 3 is wrong, since
03DEC2006-19DEC2006 is before the cycle 3 start of 20DEC2006.



On Tue, 9 Mar 2010 14:49:20 -0500, Tom Smith <need_sas_help(a)YAHOO.COM>
wrote:

>I have a following datatset "Girl" with three variables (SID1A,
>AEVSTT1D,AEVEND1D, trt)
>
>
>
> SID1A AEVSTT1D AEVEND1D trt
>
> 0150_00002 11APR2007 11APR2007 A
> 0150_00002 18APR2007 20APR2007 A
> 0150_00002 02MAY2007 04MAY2007 A
> 0150_00002 05MAY2007 07MAY2007 A
> 0150_00002 08MAY2007 08MAY2007 A
> 0150_00002 09MAY2007 10MAY2007 A
> 0150_00002 11MAY2007 16MAY2007 A
> 0150_00002 24MAY2007 24MAY2007 A
> 0150_00002 26MAY2007 26MAY2007 A
> 0150_00002 27MAY2007 27MAY2007 A
> 0150_00002 28MAY2007 28MAY2007 A
> 0150_00002 29MAY2007 29MAY2007 A
> 0150_00002 30MAY2007 31MAY2007 A
> 0150_00002 30AUG2008 30MAY2008 A
> 0012_00002 17NOV2006 23NOV2006 B
> 0012_00002 03DEC2006 19DEC2006 B
> 0012_00002 21DEC2006 26DEC2006 B
> 0012_00002 27DEC2006 19JAN2007 B
> 0012_00002 27DEC2007 29JAN2008 B
>
>
>
>
>I have another dataset "Boy" as below with three variables
>(SID1A,CYCLE,SMDDOS1D)
>
>
>
>
> SID1A CYCLE SMDDOS1D
>
> 0150_00002 1 06APR2007
> 0150_00002 2 27APR2007
> 0150_00002 3 18MAY2007
> 0012_00002 1 08NOV2006
> 0012_00002 2 29NOV2006
> 0012_00002 3 20DEC2006
>
>
>
>I need to merge them (Boy and Girl) such a way that if the value for the
>variable
>AEVSTT1D, AEVEND1D from the dataset "Girl" falls in between the 2nd value
>of SMDDOS1D
>and first value of SMDDOS1D from "Boy" (such as anything before 06APR2007
>OR such
>as (26apr2007 - 06aprl2007).or such as (17MAY2007- 27APR2007)). Also if
>last
>observation of value for the variable if trt = "A" and AEVEND1D is more
>than
>28 days after of the value of SMDDOS1D (ONLY the last observation - By
>CYCLE,of
>the dataset "Boy" )and if trt = "B"and AEVEND1D is more than
>21 days after of the value of SMDDOS1D (ONLY the last observation - By
>CYCLE, of the dataset
>"Boy" ) then they should not be included in teh final dataset.
>
>The result dataset should be as below:
>
>
>
>SID1A AEVSTT1D AEVEND1D Cycle
>
>0150_00002 11APR2007 11APR2007 1
>0150_00002 18APR2007 20APR2007 1
>0150_00002 02MAY2007 04MAY2007 2
>0150_00002 05MAY2007 07MAY2007 2
>0150_00002 08MAY2007 08MAY2007 2
>0150_00002 09MAY2007 10MAY2007 2
>0150_00002 11MAY2007 16MAY2007 2
>0150_00002 24MAY2007 24MAY2007 3
>0150_00002 26MAY2007 26MAY2007 3
>0150_00002 27MAY2007 27MAY2007 3
>0150_00002 28MAY2007 28MAY2007 3
>0150_00002 29MAY2007 29MAY2007 3
>0150_00002 30MAY2007 31MAY2007 3
>0012_00002 17NOV2006 23NOV2006 1
>0012_00002 03DEC2006 19DEC2006 3
>0012_00002 21DEC2006 26DEC2006 3
>0012_00002 27DEC2006 19JAN2007 3
>
>
>Tahnks you so much. It's complex and confusing. But if you look the final
>result dataset that
>make more sense than the description.
 | 
Pages: 1
Prev: A Complex marging Issue - Please be kind enough to take a look at
Next: how to automatically remove the first two rows in the excel file when using "View in Excel" in sas