topic Re: Create new binary var of first occurrence over multiple rows in SAS Programming

Create new binary var of first occurrence over multiple rows

Ronein — Wed, 28 Nov 2018 08:05:33 GMT

Hello

I have a raw data that contain 6 observations for each customer (during follow up period of 6 months).

For each customer there are 3 fields: ID, month, Ind

Ind is a binary variable that get value 1 if customer in fail status and 0 otherwise.

For all customers the value of var "ind" in first month is 0 (customer start is status with no failure)

The task is to create a new binary variable that will receive value 1 in first position where customer is in failure

Expected values for customer1 are:

1 1801 0 0
1 1802 0 0
1 1803 1 1
1 1804 1 0
1 1805 0 0
1 1806 1 0

Expected values for customer2 are:

2 1801 0 0
2 1802 1 1
2 1803 1 0
2 1804 1 0
2 1805 1 0
2 1806 1 0

Can anyone suggest a code that create the new field?

Data raw_tbl;
input ID date  Ind;
cards;
1 1801 0
1 1802 0
1 1803 1
1 1804 1
1 1805 0
1 1806 1
2 1801 0
2 1802 1
2 1803 1
2 1804 1
2 1805 1
2 1806 1
;
run;

Re: Create new binary var of first occurrence over multiple rows

PeterClemmensen — Wed, 28 Nov 2018 10:29:34 GMT

Data raw_tbl;
input ID date Ind;
cards;
1 1801 0
1 1802 0
1 1803 1
1 1804 1
1 1805 0
1 1806 1
2 1801 0
2 1802 1
2 1803 1
2 1804 1
2 1805 1
2 1806 1
;
run;

data want;
set raw_tbl;
by ID;
retain found;
if first.id then found=0;
flag=0;
if Ind ne 0 and found=0 then do;
flag=Ind;
found=1;
end;
drop found;
run;

Re: Create new binary var of first occurrence over multiple rows

novinosrin — Wed, 28 Nov 2018 15:04:55 GMT

Data have;
input ID date Ind;
cards;
1 1801 0
1 1802 0
1 1803 1
1 1804 1
1 1805 0
1 1806 1
2 1801 0
2 1802 1
2 1803 1
2 1804 1
2 1805 1
2 1806 1
;
run;

proc sql;
create table want as
select *,(min(date)=date)*(ind=1) as new
from have
group by id,ind
order by id,date;
quit;