SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How do I create monthly observations from Yearly

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

How do I create monthly observations from Yearly

Hello Everyone,

 

I am trying to down sample a dataset from yearly frequency to monthly. I would really appreciate if some one could guide me to do so in a data step. I will explain what I am trying to do with an example:

 

Have                                                       

IDDateRank
000112/31/20122
000112/31/20132
00026/30/20064
000212/31/20075

 

Need

IDDateRank
000112/31/20122
00011/31/20132
00012/28/20132
00013/31/20132
00014/30/20132
00015/31/20132
00016/30/20132
00017/31/20132
00018/31/20132
00019/30/20132
000110/31/20132
000111/30/20132
000112/31/20133
00011/31/20143
   
   
000111/30/20143
   
Similarly for the other ID

 

Thanks


Accepted Solutions
Solution
‎05-27-2016 01:28 PM
Super User
Posts: 10,023

Re: How do I create monthly observations from Yearly

data have;
infile cards expandtabs truncover;
input ID 	Date : mmddyy10.	Rank;
format Date  mmddyy10.;
cards;
0001	12/31/2012	2
0001	12/31/2013	2
0002	6/30/2006	4
0002	12/31/2007	5
;
run;
data want;
 merge have have(keep=id date rename=(id=_id date=_date) firstobs=2);
 output;
 if id=_id then do;
  temp=date;
  do i=1 to intck('month',temp,_date)-1;
   date=intnx('month',temp,i,'e');output;
  end;
 end;
 drop i temp _:;
run;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: How do I create monthly observations from Yearly

Given this data:

0001 12/31/2012 2
0001 12/31/2013 2

what is the rule to create records to 11/30/2014?

How do we know that the rank becomes 3 in the output for 12/31/2013? And continues with teh same rank to 11/30/2014?

Contributor
Posts: 34

Re: How do I create monthly observations from Yearly

Hi Ballardw

 

That is a typo, the rank remains 2 all the way to 11/30/2014. The rank only changes if there is a change in the original data. I am reposting the Need table.

 

Need

IDDateRank
000112/31/20122
00011/31/20132
00012/28/20132
00013/31/20132
00014/30/20132
00015/31/20132
00016/30/20132
00017/31/20132
00018/31/20132
00019/30/20132
000110/31/20132
000111/30/20132
000112/31/20132
00011/31/20142
   
   
000111/30/20142
   
Similarly for the other ID

 

Thanks

Super User
Posts: 11,343

Re: How do I create monthly observations from Yearly

Still need the stopping rule.

Does Id 0002 continue on to 11/30/2008 or 11/30/2014 or something else?

Contributor
Posts: 34

Re: How do I create monthly observations from Yearly

Hi Ballardw,

 

This is a snippet of the dataset. For ID 0002, if that is the last observation in the dataset then it will stop on 11/30/2008 other wise it will stop one month prior to the date value for the next observation. 

 

Thanks 

Super User
Posts: 11,343

Re: How do I create monthly observations from Yearly

I think this will get you started:

Note: if you have more than 2 values for ID there's going to be overlapping output.

/* assumes data is sorted by ID and date*/
data want (keep=id outdate outrank rename=(outdate=date outrank=rank)) ;
   set example;
   by id date;
   retain FirstDate FirstRank;
   if first.id then do;
      FirstDate=Date;
      FirstRank=rank;
   end;
   else do;
      /* First to output values from the first date up to the second date*/
      outdate=firstdate;
      outrank=firstrank;
      do until (outdate > intnx('month',date,-1,'e'));
         output;
         outdate = intnx('month',outdate,1,'e');
      end;
      outdate = date;
      outrank = rank;
      do until (outdate > intnx('month',date,11,'e'));
         output;
         outdate = intnx('month',outdate,1,'e');
      end;
      
   end;
   format firstdate outdate mmddyy10.;
run;
Solution
‎05-27-2016 01:28 PM
Super User
Posts: 10,023

Re: How do I create monthly observations from Yearly

data have;
infile cards expandtabs truncover;
input ID 	Date : mmddyy10.	Rank;
format Date  mmddyy10.;
cards;
0001	12/31/2012	2
0001	12/31/2013	2
0002	6/30/2006	4
0002	12/31/2007	5
;
run;
data want;
 merge have have(keep=id date rename=(id=_id date=_date) firstobs=2);
 output;
 if id=_id then do;
  temp=date;
  do i=1 to intck('month',temp,_date)-1;
   date=intnx('month',temp,i,'e');output;
  end;
 end;
 drop i temp _:;
run;
Contributor
Posts: 34

Re: How do I create monthly observations from Yearly

Thank you Ksharp and ballardw for the solutions.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 506 views
  • 3 likes
  • 3 in conversation