Reputation: 93
Hello I need to create dummy date records per subjid. So for example 10001 start date (ASTDT) is 01DEC2019 and End date (AENDT) IS 03DEC2019 so I need to create a dummy record for 02DEC2019 for that subject ID. I'm assuming this may require a proc transpose at some point and I've seen relevant code with similar logic like this but not exactly what I need
data want;
set have;
by account;
output;
if last.account then do;
/*Current month as a number*/
month_n = month(input(catt("01",strip(month),"2000"),date9.));
/*LastMonth as a number*/
to_month = month(input(catt("01",lastMonth,"2000"),date9.));
do i=month_n+1 to to_month;
month = put(mdy(i,1,2000),monname3.); /*Increment the month and write the month name*/
output;
end;
end;
drop month_n to_month i;
run;
This is the desired output
100001 01DEC2019 03DEC2019
100001 02DEC2019 03DEC2019
100001 03DEC2019 03DEC2019
100002 30JAN2020 31JAN2020
100002 31JAN2020 31JAN2020
100002 31JAN2020 02FEB2020
100002 01FEB2020 02FEB2020
100002 02FEB2020 02FEB2020
100002 31JAN2020 31JAN2020
100003 24FEB2020 24FEB2020
100003 21FEB2020 22FEB2020
100003 22FEB2020 22FEB2020
Upvotes: 0
Views: 164
Reputation: 4947
data want(drop = ASTDT);
format subjid dt AENDT;
set have;
do dt = ASTDT to AENDT;
dummy = not (dt = ASTDT | dt = AENDT);
output;
end;
format dt date9.;
run;
Result:
Obs subjid dt AENDT dummy
1 100001 01DEC2019 03DEC2019 0
2 100001 02DEC2019 03DEC2019 1
3 100001 03DEC2019 03DEC2019 0
4 100002 30JAN2020 31JAN2020 0
5 100002 31JAN2020 31JAN2020 0
6 100002 31JAN2020 02FEB2020 0
7 100002 01FEB2020 02FEB2020 1
8 100002 02FEB2020 02FEB2020 0
9 100002 31JAN2020 31JAN2020 0
10 100003 24FEB2020 24FEB2020 0
11 100003 21FEB2020 22FEB2020 0
12 100003 22FEB2020 22FEB2020 0
Upvotes: 0
Reputation: 4947
See if this meets your needs
data have;
input subjid $ (ASTDT AENDT)(:date9.);
format ASTDT AENDT date9.;
datalines;
100001 01DEC2019 03DEC2019
100002 30JAN2020 31JAN2020
100002 31JAN2020 02FEB2020
100002 31JAN2020 31JAN2020
100003 24FEB2020 24FEB2020
100003 21FEB2020 22FEB2020
;
data want;
set have;
do ASTDT = ASTDT to AENDT;
output;
end;
run;
Result:
Obs subjid ASTDT AENDT
1 100001 01DEC2019 03DEC2019
2 100001 02DEC2019 03DEC2019
3 100001 03DEC2019 03DEC2019
4 100002 30JAN2020 31JAN2020
5 100002 31JAN2020 31JAN2020
6 100002 31JAN2020 02FEB2020
7 100002 01FEB2020 02FEB2020
8 100002 02FEB2020 02FEB2020
9 100002 31JAN2020 31JAN2020
10 100003 24FEB2020 24FEB2020
11 100003 21FEB2020 22FEB2020
12 100003 22FEB2020 22FEB2020
Upvotes: 1
Reputation: 4947
For starters, let us assunme that your data looks like below.
What does your desired result look like?
data have;
input subjid $ (ASTDT AENDT)(:date9.);
format ASTDT AENDT date9.;
datalines;
100001 01DEC2019 03DEC2019
100002 30JAN2020 31JAN2020
100002 31JAN2020 02FEB2020
100002 31JAN2020 31JAN2020
100003 24FEB2020 24FEB2020
100003 21FEB2020 22FEB2020
;
Upvotes: 1