topic Deriving data values from first value of each observation into new variable in New SAS User

Deriving data values from first value of each observation into new variable

tsureshinvites — Wed, 18 Dec 2019 01:49:19 GMT

data sv_raw;
input PT_ID $ Visit $ Res ;
datalines;
1 1 20
1 2 40
1 3 30
2 1 30
2 2 40
2 3 50
;

Need output: The result of first value of patient should be populated in Base (Variable) like below.

objective is New variable has to be created in the name of base, the data value is 'first value of res(result) variable'

PT_ID	Visit	Res	Base
1	1	20	20
1	2	40	20
1	3	30	20
2	1	30	30
2	2	40	30
2	3	50	30

My code;

data sv_raw;
input PT_ID $ Visit $ Res $;
datalines;
1 1 20
1 2 40
1 3 30
2 1 30
2 2 40
2 3 50
;

proc sort data=sv_raw out=sv;
by PT_ID Visit Res;
run;

data baseline;
set sv;
by PT_ID Visit Res;
if not missing(PT_ID)then Base = first.res;
run;

Re: Deriving data values from first value of each observation into new variable

novinosrin — Wed, 18 Dec 2019 01:59:14 GMT

hi @tsureshinvites are you asking for this ?

data sv_raw;
input PT_ID $ Visit $ Res ;
datalines;
1 1 20
1 2 40
1 3 30
2 1 30
2 2 40
2 3 50
;

data want;
set sv_raw;
by PT_ID;
retain base;
if first.PT_ID then base=res;
run;

Re: Deriving data values from first value of each observation into new variable

novinosrin — Wed, 18 Dec 2019 02:08:19 GMT

data sv_raw;
input PT_ID $ Visit $ Res ;
datalines;
1 1 20
1 2 40
1 3 30
2 1 30
2 2 40
2 3 50
;

proc sql;
create table want as
select a.*, base
from sv_raw a,(select pt_id,res as base from sv_raw where visit='1') b
where a.pt_id=b.pt_id
order by pt_id, visit;
quit;

Re: Deriving data values from first value of each observation into new variable

mkeintz — Wed, 18 Dec 2019 03:55:27 GMT

This might be a good situation for a merge statement, as in:

data sv_raw;
input PT_ID $ Visit  Res ;
datalines;
1 1 20
1 2 40
1 3 30
2 1 30
2 2 40
2 3 50
run;

data want;
  merge sv_raw (keep=pt_id visit res rename=(res=base) where=(visit=1))
        sv_raw;
  by pt_id;
run;

This program assumes that:

The data are sorted by PT_ID
Each PT_ID has one record with visit=1.

The program is a 1-to-many match merge based on PT_ID.

if matches a subset of SV_RAW (namely only observations with visit=1) will all of SV_RAW, matched on PT_ID.

But both the subset and the complete set have a variable named RES, so to avoid a "collision" I rename the RES in the subset to BASE.

You might note there would be a collision of VISIT also. But when there is a collision, it will be the latter instance of visit (from the complete set) that overwrites the value from the earlier instance (the visit=1 subset). In other words, the order of the two objects of the MERGE statement matters.

This order would be wrong
merge have

have (keep=pt_id visit res rename(res=base) where=(visit=1));

because the value of visit=1 would overwrite all the visit values from the other records.