topic Re: add a dummy in SAS Procedures

add a dummy

sasphd — Fri, 12 Feb 2021 18:06:19 GMT

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

Re: add a dummy

Reeza — Fri, 12 Feb 2021 18:26:42 GMT

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

Re: add a dummy

sasphd — Fri, 12 Feb 2021 18:43:51 GMT

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

Re: add a dummy

PeterClemmensen — Fri, 12 Feb 2021 19:26:15 GMT

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;

Re: add a dummy

ballardw — Fri, 12 Feb 2021 19:40:58 GMT

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?

Re: add a dummy

Reeza — Fri, 12 Feb 2021 20:34:56 GMT

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

Re: add a dummy

sasphd — Fri, 12 Feb 2021 20:39:17 GMT

thank you for the good question. I did not have that in this case I check for the difference between the transaction date to be more than 4