How to assign a base value to OBS with same subject

[knveraraju91]

Dear,

In my data, I need assign a base value to OBS of that subject.

From my data I need to take the value when visit=1 and populate to all subjects.

 code:

proc sql;
create table data2 as 
select *,value as base
from data1
where visit=1
group by id;
quit;

data1

id        visit               value

1          1                     10

1           2                   20

2           1                   20

2           1                    20

output need:

data2

data

id        visit               value              base

1          1                     10                 10

1           2                   20                   10

2           1                   20                    20

2           1                    30                    20

[Shmuel]

It is simple and easy to be done by data step:

Data data2;

  set data1;

   by id;

        retain base;

        if first.id then base=.;

        if visit=1 then base=value;

run;

[RW9]

Post your test data in the form of a datastep in future, I am not here to type in test data for you.  If you need to do this with SQL, then something along the lines of:

select A.*,
         B.VAL as BASE
from   HAVE A
left join (select * from HAVE where VISIT=1) B
on     ...