Lapis Lazuli | Level 10

I want to add a column of dummy if tranasaction date equal date then add D=1 for that date and the next three date

table have

 number date transaction date 1 20150911 . 1 20150912 . 1 20150913 20150913 1 20150914 . 1 20150915 . 1 20150916 .

table want

 number date transaction date D 1 20150911 . 0 1 20150912 . 0 1 20150913 20150913 1 1 20150914 . 1 1 20150915 . 1 1 20150916 . 1
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

A slight modification of @Reezas code gives you what you want

``````data have;
input number (date transactiondate)(:yymmdd8.);
format date transactiondate yymmddn8.;
datalines;
1 20150911 .
1 20150912 .
1 20150913 20150913
1 20150914 .
1 20150915 .
1 20150916 .
1 20150917 .
;

data want(drop = counter);
set have;
by number date;

*assumes you have more than one number and need to do this for multiple numbers;
if first.number then counter=0;

if date=transactionDate then counter=5;
counter +- 1;
if counter > 0 then D=1;

run;``````
6 REPLIES 6
Super User

``````data want;
set have;
by number date;

*assumes you have more than one number and need to do this for multiple numbers;
if first.number then counter=0;

if date=transactionDate then counter=4;
counter -1;
if counter > 0 then D=1;

run;``````

I'm assuming you'll have multiple numbers but this should work.

Set a counter to 4 when you find the first match and then decrement it each row. If the value is still above 0, then D=1, or if you reach a new "number" you reset it.

@sasphd wrote:

I want to add a column of dummy if tranasaction date equal date then add D=1 for that date and the next three date

table have

 number date transaction date 1 20150911 . 1 20150912 . 1 20150913 20150913 1 20150914 . 1 20150915 . 1 20150916 .

table want

 number date transaction date D 1 20150911 . 0 1 20150912 . 0 1 20150913 20150913 1 1 20150914 . 1 1 20150915 . 1 1 20150916 . 1

Lapis Lazuli | Level 10

yes I have multiple number. but I want the dummy D=1 only for the transaction date et three dates after then it will be D=0

like this table

table want

 number date transaction date D 1 20150911 . 0 1 20150912 . 0 1 20150913 20150913 1 1 20150914 . 1 1 20150915 . 1 1 20150916 . 1 1 20150917 . 0

Super User

A slight modification of @Reezas code gives you what you want

``````data have;
input number (date transactiondate)(:yymmdd8.);
format date transactiondate yymmddn8.;
datalines;
1 20150911 .
1 20150912 .
1 20150913 20150913
1 20150914 .
1 20150915 .
1 20150916 .
1 20150917 .
;

data want(drop = counter);
set have;
by number date;

*assumes you have more than one number and need to do this for multiple numbers;
if first.number then counter=0;

if date=transactionDate then counter=5;
counter +- 1;
if counter > 0 then D=1;

run;``````
Super User

Just add an ELSE D=0 after then. It really helps if you include more representative data at the start.
Super User

What should happen if you have another transaction date equal to the date before running out of the previous "3 following" like if the data were:

 number date transaction date 1 20150911 . 1 20150912 . 1 20150913 20150913 1 20150914 . 1 20150915 . 1 20150916 20150916

Do want a "2" for the overlap or similar? Does this start new sequence or not?

Lapis Lazuli | Level 10