producing data between given months and years

[judyluu]

So I have this table:

MONTH	YEAR	VALUE
10	2021	2883451.22
2	2021	1357309.65
6	2021	3118489.88
10	2021	1116031.14
12	2021	2913261.84
4	2022	122561.664
6	2022	189398.027
10	2022	121688.232

and need to code it to where it takes data from this table to make it look like this table:

MONTH	YEAR	VALUE
10	2020	1357309.649
11	2020	1357309.649
12	2020	1357309.649
1	2021	1357309.649
2	2021	3118489.879
3	2021	3118489.879
4	2021	3118489.879
5	2021	3118489.879
6	2021	1116031.145
7	2021	1116031.145
8	2021	1116031.145
9	2021	1116031.145
10	2021	2913261.84
11	2021	2913261.84
12	2021	122561.6642
1	2022	122561.6642
2	2022	122561.6642
3	2022	122561.6642
4	2022	189398.0267
5	2022	189398.0267
6	2022	121688.2321
7	2022	121688.2321
8	2022	121688.2321
9	2022	121688.2321

For a row in the current table, the new table looks at the previous month and year and outputs all the months and years between the observations with that value (including the month, year for the previous observation but not the month, year for the row looked at)

I need help coding this in SAS 

Thanks in advance! 

[ballardw]

I think you need to expand your "have" and walk through exactly how things are selected. Your results show values for the year 2020 and months in 2021 that are not in the "have" example. Where do they come from?

---

@judyluu wrote:

So I have this table:

MONTH	YEAR	VALUE
10	2021	2883451.22
2	2021	1357309.65
6	2021	3118489.88
10	2021	1116031.14
12	2021	2913261.84
4	2022	122561.664
6	2022	189398.027
10	2022	121688.232

and need to code it to where it takes data from this table to make it look like this table:

MONTH	YEAR	VALUE
10	2020	1357309.649
11	2020	1357309.649
12	2020	1357309.649
1	2021	1357309.649
2	2021	3118489.879
3	2021	3118489.879
4	2021	3118489.879
5	2021	3118489.879
6	2021	1116031.145
7	2021	1116031.145
8	2021	1116031.145
9	2021	1116031.145
10	2021	2913261.84
11	2021	2913261.84
12	2021	122561.6642
1	2022	122561.6642
2	2022	122561.6642
3	2022	122561.6642
4	2022	189398.0267
5	2022	189398.0267
6	2022	121688.2321
7	2022	121688.2321
8	2022	121688.2321
9	2022	121688.2321

For a row in the current table, the new table looks at the previous month and year and outputs all the months and years between the observations with that value (including the month, year for the previous observation but not the month, year for the row looked at)

I need help coding this in SAS 

Thanks in advance! 

---

[judyluu]

so starting with the second row 

2

2021

1357309.65

in the table I have, I look at the previous observation

MONTH	YEAR
10	2021

and see for the prior month it was October. So knowing that I want my new table to produce October 2021, November 2021, December 2021, and January 2022 (not including February the current observation) using the value 1357309.65 in the second row from the table I have.

is it would look like this:

MONTH	YEAR	VALUE
10	2020	1357309.649
11	2020	1357309.649
12	2020	1357309.649
1	2021	1357309.649

Then it's the same so on. I hope this makes things clearer

[ballardw]

---

@judyluu wrote:

so starting with the second row 

2

2021

1357309.65

in the table I have, I look at the previous observation

MONTH	YEAR
10	2021

and see for the prior month it was October. So knowing that I want my new table to produce October 2021, November 2021, December 2021, and January 2022 (not including February the current observation) using the value 1357309.65 in the second row from the table I have.

is it would look like this:

MONTH	YEAR	VALUE
10	2020	1357309.649
11	2020	1357309.649
12	2020	1357309.649
1	2021	1357309.649

Then it's the same so on. I hope this makes things clearer

---

So why does the result show the YEAR as 2020???? You said that you want October 2021, November 2021, December 2021 but then repeat showing 2020 as the result. Which is it?

[judyluu]

I see what went wrong. so the First row should be 

MONTH	YEAR
10	2020

So knowing that I want my new table to produce October 2020, November 2020, December 2020, and January 2021 (not including February the current observation) using the value 1357309.65 in the second row from the table I have.

so it would look like this:

MONTH	YEAR	VALUE
10	2020	1357309.649
11	2020	1357309.649
12	2020	1357309.649
1	2021	1357309.649

[Kurt_Bremser]

Your "have" dataset does not have values for 2020. It starts with 2021 (and is not sorted by year and month, BTW).

From where do you take it that you wan to start with October 2020?

[judyluu]

Right. The first row should have said October 2020. Not October 2021. Sorry about the confusion

[Reeza]

I'm guessing you have another grouping variable in this data that separates the sequences?

[judyluu]

right. It's grouped by months and I need to break it out by months.

[judyluu]

MONTH	YEAR	VALUE
10	2020	2883451.22
2	2021	1357309.65
6	2021	3118489.88
10	2021	1116031.14
12	2021	2913261.84
4	2022	122561.664
6	2022	189398.027
10	2022	121688.232

Sorry about that. This is the correct table. 

[Tom]

So you want to fill in the missing months, retaining the previous value. It would be easier to have an actual DATE variable instead of two separate variables.

If you generate a list of the dates to be included and then just interleave the two lists and remember the VALUE it can work.

For example here is a data step that calculates the min and max dates and then generates one observation per month.

data months;
  set have end=eof;
  date=mdy(month,1,year);
  min=min(date,min);
  max=max(date,max);
  retain min max;
  if eof then do offset=0 to intck('month',min,max);
    date=intnx('month',min,offset);
    month=month(date);
    year=year(date);
    output;
  end;
  keep month year;
run;

Now just interleave them and remember the VALUE into a new retained variable.

data want;
  set have(in=in1) months(in=in2);
  by year month;
  if in1 then new_value=value;
  retain new_value;
  if in2;
  keep month year new_value;
run;

Or since you just have one observations per date you could just use merge

data want;
  merge months have;
  by year month;
  new_value=coalesce(value,new_value);
  retain new_value;
run;

[Ksharp]

data have;
infile cards expandtabs;
input MONTH YEAR VALUE;
cards;
10 2020 2883451.22
2 2021 1357309.65
6 2021 3118489.88
10 2021 1116031.14
12 2021 2913261.84
4 2022 122561.664
6 2022 189398.027
10 2022 121688.232
;

proc sort data=have;by year month;run;
data want;
 merge have have(firstobs=2 keep=MONTH YEAR VALUE
 rename=(MONTH=_MONTH YEAR=_YEAR VALUE=_VALUE)) end=last;
output;
if not last then do;
do date=intnx('month',mdy(MONTH,1,YEAR),1) to intnx('month',mdy(_MONTH,1,_YEAR),-1);
 if temp ne month(date) then do;
   month=month(date);
   year=year(date);
   VALUE=_VALUE;
   output;
 end;
 temp=month(date);
end;
end;
keep MONTH YEAR VALUE;
run;

[Patrick]

If you've got SAS/ETS licensed then you could let Proc Expand do the work for you.

data have;
  infile cards expandtabs;
  input MONTH YEAR VALUE;
  date=mdy(month,01,year);
  format date monyy7.;
  cards;
10 2020 2883451.22
2 2021 1357309.65
6 2021 3118489.88
10 2021 1116031.14
12 2021 2913261.84
4 2022 122561.664
6 2022 189398.027
10 2022 121688.232
;

proc expand 
  data=have
  out=want
  to=month
  ;
  id date;
  convert value / method=step;
run;

proc print data=want;
run;