topic Re: Create a yearly file data set from 2 years in SAS Data Management

Create a yearly file data set from 2 years

vapodaca11 — Sat, 22 Jun 2019 13:49:54 GMT

I am trying to create a yearly observation between 20 years that expands beyond the years.

What I have

Location Year Num

A 1980 123

A 2010 496

B 1980 1846

B 2010 1776

What I want is to create a yearly observation while retaining the Location but not the Num variable. Specifically I need to go from 1980 to 2012.

What it should look like:

Location Year Num

A 1980 123

A 1981

A ......

A 2009

A 2010 496

A 2011

A 2012

Re: Create a yearly file data set from 2 years

Astounding — Sat, 22 Jun 2019 18:46:38 GMT

Unfortunately, it takes a few steps. But they're all short:

data years;
   do year = 1980 to 2012;
      output;
   end;
run;

proc sql;
   create table locations as select distinct location from have;
   create table shell as select * from locations, years order by location, year;
quit;

data want;
   merge shell have;
   by location year;
run;

It's untested code, so see if it works for you.

****** Corrected ... thanks @VDD

Re: Create a yearly file data set from 2 years

VDD — Sat, 22 Jun 2019 15:21:21 GMT

you will need a comma in the by statement.

create table shell as select * from locations, years order by location year;

should be

create table shell as select * from locations, years order by location, year;