topic Re: Adding Observations by Year in SAS Programming

Adding Observations by Year

jjadall1 — Thu, 26 Nov 2015 16:51:59 GMT

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

Re: Adding Observations by Year

pearsoninst — Thu, 26 Nov 2015 16:55:13 GMT

If you give an example data it will help

Re: Adding Observations by Year

jjadall1 — Thu, 26 Nov 2015 17:01:19 GMT

The dataset I'm working with is attached.

Thanks so much!

Jadallah

Re: Adding Observations by Year

pearsoninst — Thu, 26 Nov 2015 19:15:48 GMT

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;

Re: Adding Observations by Year

Patrick — Thu, 26 Nov 2015 19:38:48 GMT

@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;

Re: Adding Observations by Year

pearsoninst — Thu, 26 Nov 2015 19:53:23 GMT

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

Re: Adding Observations by Year

Patrick — Thu, 26 Nov 2015 20:09:26 GMT

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

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

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

Re: Adding Observations by Year

Patrick — Thu, 26 Nov 2015 21:09:05 GMT

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#p1cydk5fq0u4bfn1xfbjt7w1c7lu.htm

Re: Adding Observations by Year

jjadall1 — Fri, 27 Nov 2015 11:26:43 GMT

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.