How to extend the current data set with a short time period to describe the full time frame

[CynthiaWei]

Hi SAS Pros,

I have a dataset like this:

ID	Yes_or_No	count_of_month	month
1	1	9	2017-03
1	1	9	2017-04
1	1	9	2017-05
1	1	9	2017-06
1	1	9	2017-07
1	1	9	2017-08
1	1	9	2017-09
1	1	9	2017-10
1	1	9	2017-11
1	1	9	2017-12
2	1	6	2017-06
2	1	6	2017-07
2	1	6	2017-08
2	1	6	2017-09
2	1	6	2017-10
2	1	6	2017-11
3	0	0	.
3	0	0	.
3	0	0	.
3	0	0	.
4	0	0	.

My next step is to (what I want):

1. Create a new variable called calendar_month with values from 2017-01 to 2018-04 (16 rows for each person)

2. Create another variable called person_permonth to represent the 'month' that the duration covered, for example, person 1 has 2017-03 in the month, then person_permonth=1 for calendar_month=2017-03; person 1 doesn't have 2017-01 and 2017-02, so person_permonth=0 for calendar_month=2017-01 and 2017-02 as follows. By doing this, every person has 16 rows with each row represent one month from 2017-01 to 2018-04. If the person is Yes_or_No=1 and has certain months in the month variable, the corresponding months under calendar_month should be 1, if the person doesn't have certain months in the variable month, the corresponding months under variable calendar_month is 0. If the person is Yes_or_No=0, the every month under calendar_month should be 0 for all 16 rows for that person. Hope I explain it clearly.

ID	Yes_or_No	count_of_month	month	calendar_month	person_permonth
1	1	9		2017-01	0
1	1	9		2017-02	0
1	1	9	2017-03	2017-03	1
1	1	9	2017-04	2017-04	1
1	1	9	2017-05	2017-05	1
1	1	9	2017-06	2017-06	1
1	1	9	2017-07	2017-07	1
1	1	9	2017-08	2017-08	1
1	1	9	2017-09	2017-09	1
1	1	9	2017-10	2017-10	1
1	1	9	2017-11	2017-11	1
1	1	9	2017-12	2017-12	1
1	1	9		2018-01	0
1	1	9		2018-02	0
1	1	9		2018-03	0
1	1	9		2018-04	0
2	1	6		2017-01	0
2	1	6		2017-02	0
2	1	6		2017-03	0
2	1	6		2017-04	0
2	1	6		2017-05	0
2	1	6	2017-06	2017-06	1
2	1	6	2017-07	2017-07	1
2	1	6	2017-08	2017-08	1
2	1	6	2017-09	2017-09	1
2	1	6	2017-10	2017-10	1
2	1	6	2017-11	2017-11	1
2	1	6		2017-12	0
2	1	6		2018-01	0
2	1	6		2018-02	0
2	1	6		2018-03	0
2	1	6		2018-04	0
3	0	0	.	2017-01	0
3	0	0	.	2017-02	0
3	0	0	.	2017-03	0
3	0	0	.	2017-04	0
3	0	0	.	2017-05	0
3	0	0	.	2017-06	0
3	0	0	.	2017-07	0
3	0	0	.	2017-08	0
3	0	0	.	2017-09	0
3	0	0	.	2017-10	0
3	0	0	.	2017-11	0
3	0	0	.	2017-12	0
3	0	0	.	2018-01	0
3	0	0	.	2018-02	0
3	0	0	.	2018-03	0
3	0	0	.	2018-04	0
4	0	0	.	2017-01	0
4	0	0	.	2017-02	0
4	0	0	.	2017-03	0
4	0	0	.	2017-04	0
4	0	0	.	2017-05	0
4	0	0	.	2017-06	0
4	0	0	.	2017-07	0
4	0	0	.	2017-08	0
4	0	0	.	2017-09	0
4	0	0	.	2017-10	0
4	0	0	.	2017-11	0
4	0	0	.	2017-12	0
4	0	0	.	2018-01	0
4	0	0	.	2018-02	0
4	0	0	.	2018-03	0
4	0	0	.	2018-04	0

I know it is a little complicated. I appreciate any help with this project!

Best regards,

C

[andreas_lds]

Please post what you have in usable form: a data  step using datalines. Without knowing the types of the variables, it is hardly possible to suggest something that will actually work with your data.

Before starting to solve the problem, make sure that "month" is a sas date formatted to show only year and month.

[CynthiaWei]

Hi,

Here is the datalines:

data have;
input ID Yes_or_NO count_of_month month $;
datalines;
1 1 9 2017-03
1 1 9 2017-04
1 1 9 2017-05
1 1 9 2017-06
1 1 9 2017-07
1 1 9 2017-08
1 1 9 2017-09
1 1 9 2017-10
1 1 9 2017-11
1 1 9 2017-12
2 1 6 2017-06
2 1 6 2017-07
2 1 6 2017-08
2 1 6 2017-09
2 1 6 2017-10
2 1 6 2017-11
3 0 0 .
3 0 0 .
3 0 0 .
3 0 0 .
4 0 0 .
;
run;

Hope it is usable.

Thanks a lot!

[RichardDeVen]

Create a separate data set of the 16 months you want each person to have.  Cross join that with a distinct selection of person id to get complete month list per person, follow up with left joining to original data.

Example:

data have; input 
ID Flag count month; 
attrib month format=yymmd. informat=ANYDTDTE.;
datalines;
1 1 9 2017-03
1 1 9 2017-04
1 1 9 2017-05
1 1 9 2017-06
1 1 9 2017-07
1 1 9 2017-08
1 1 9 2017-09
1 1 9 2017-10
1 1 9 2017-11
1 1 9 2017-12
2 1 6 2017-06
2 1 6 2017-07
2 1 6 2017-08
2 1 6 2017-09
2 1 6 2017-10
2 1 6 2017-11
3 0 0 .
3 0 0 .
3 0 0 .
3 0 0 .
4 0 0 .
;

* list of wanted months;
data months;
  do month = '01jan2017'd to '01APR2018'd by 1;
    output;
    month = intnx('month', month, 0, 'E');
  end;
  format month yymmd.;
run;

proc sql;
  create table want as
  select 
    ids.id
  , have.flag
  , have.count
  , months.month as month
  from
    (select distinct id from have) as ids  /* individual ids */
  cross join
    months                                 /* wanted months */ 
  left join 
    have                                   /* original data */
  on
    have.id = ids.id
  & have.month = months.month
  order by
    ids.id, months.month
  ;