DATA Step, Macro, Functions and more

Adding Observations by Year

Reply
Contributor
Posts: 38

Adding Observations by Year

Hello,

I currently have a dataset with 1 observation per firm in various years.  For example, I have a firm with a 1998 observation.  Is there a way to code it where I can get lines for 1995, 1996, 1997, 1999, 2000, and 2001 observations for the same firm (3 years before and 3 years after)?  That way, instead of having 1 observation per firm, I will have 7.  Finally, I would like to delete the original observation (1998 in my example).

 

Thanks so much!

Jadallah

Frequent Contributor
Posts: 108

Re: Adding Observations by Year

If you give an example data it will help
Contributor
Posts: 38

Re: Adding Observations by Year

The dataset I'm working with is attached.

 

Thanks so much!

Jadallah

Frequent Contributor
Posts: 108

Re: Adding Observations by Year

This code is working , but i think you get better soluation in this forum . 

data newdata; Input Id$ Year; datalines; A 1998 ; run; Data want1; set newdata; Do i = 1 to 3; Year1 = Year+(i); Output; End; drop i year; run; Data want2; set newdata; Do i = 1 to 3; Year2 = Year-(i); Output; End; drop i year; run; Proc SQL; Select* from want1 union All Select* from want2; quit;

 

Respected Advisor
Posts: 3,889

Re: Adding Observations by Year

[ Edited ]

@pearsoninst

An simpler coding variant for what you're proposing is:

data have;
  input Id$ Year;
  datalines;
A 1998
;
run;
data want(drop=_y);
  set have(rename=(year=_y));
  do year= _y-3 to _y-1 , _y+1 to _y+3 ;
    output;
  end;
run;
 

 

Frequent Contributor
Posts: 108

Re: Adding Observations by Year

Cool one Patrick, I was little lost with the below code
do year= _y-3 to _y-1 , _y+1 to _y+3 ;
Respected Advisor
Posts: 3,889

Re: Adding Observations by Year

[ Edited ]

The do loop allows for quite a few different variations of syntax. 

Have a look at Example1: 

https://support.sas.com/documentation/cdl/en/lestmtsref/68024/HTML/default/viewer.htm#p1cydk5fq0u4bf...

 

Respected Advisor
Posts: 3,889

Re: Adding Observations by Year

[ Edited ]

What should happen if a firm has multiple entries in your source dataset?

Can you please show us your desired result for _cik=0001046649

Capture.PNG

 

Not my area of expertise but should you intend to create such data as preparation for time series then look into Proc Expand.

Contributor
Posts: 38

Re: Adding Observations by Year

Thanks for the help - God bless you and your family!  The reason there are multiple entries of the same firm is because I have a treatment group and a control group; with the control group, I did matching with replacement.  It would be great to know how to do matching without replacement for the future.

Ask a Question
Discussion stats
  • 8 replies
  • 558 views
  • 2 likes
  • 3 in conversation