DATA Step, Macro, Functions and more

How can I add some missing observations in a dataset?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

How can I add some missing observations in a dataset?

Hi all,

I have this data set on firm-years with their location addresses from 1990-2005. The original data set has not the address for all of the years, so there is no observation included for that specific year. However, I want to add those firm-years to the data set in an efficinet way.

So, this is my current data set:

FirmYearAddress
x1990xxx
x1991xxx
x1994xxx
x1996yyy

and I want to convert in into:

Header 1Header 2Header 3
x1990xxx
x1991xxx
x1992---
x1993---
x1994xxx
x1995---
x1996yyy

Can anyone please help me how to do that efficiently?

Thanks a lot!


Accepted Solutions
Solution
‎12-19-2013 04:47 PM
Respected Advisor
Posts: 3,156

Re: How can I add some missing observations in a dataset?

Posted in reply to Shayan2012

This I believe has been asked and answered many times, so do a search you will learn more solutions, the following is just one of them.

data have;

input Firm$ Year Address$;

cards;

x 1990 xxx

x 1991 xxx

x 1994 xxx

x 1996 yyy

;

data want;

  merge have have(keep=year firm rename=(year=_y firm=_f) firstobs=2);

output;

if firm=_f then do year=year+1 to _y-1;

call missing (address);

  output;

  end;

drop _:;

run;

Haikuo

Last version was not rigorously tested, hence the update.

View solution in original post


All Replies
Solution
‎12-19-2013 04:47 PM
Respected Advisor
Posts: 3,156

Re: How can I add some missing observations in a dataset?

Posted in reply to Shayan2012

This I believe has been asked and answered many times, so do a search you will learn more solutions, the following is just one of them.

data have;

input Firm$ Year Address$;

cards;

x 1990 xxx

x 1991 xxx

x 1994 xxx

x 1996 yyy

;

data want;

  merge have have(keep=year firm rename=(year=_y firm=_f) firstobs=2);

output;

if firm=_f then do year=year+1 to _y-1;

call missing (address);

  output;

  end;

drop _:;

run;

Haikuo

Last version was not rigorously tested, hence the update.

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 209 views
  • 1 like
  • 2 in conversation