topic Re: How can I expand a dataset into a panel? in SAS Programming

How can I expand a dataset into a panel?

xyxu — Sun, 24 Jun 2018 17:22:36 GMT

I have a dataset as follows.

variable: ID

obs1: A

obs2: B

obs3: C

I hope to expand this dataset to a panel, where each ID corresponds to a time series:

variable: ID year

obs1: A 2001

obs2: A 2002

obs3: A 2003

obs4: B 2001

obs5: B 2002

obs6: B 2003

obs7: C 2001

obs8: C 2002

obs9: C 2003

Which command can perform this expasion?

Re: How can I expand a dataset into a panel?

Kurt_Bremser — Mon, 25 Jun 2018 08:27:16 GMT

data want;
set have;
do year = 2001 to 2003; /* create three observations for every obs in the input dataset */
  output;
end;
run;

Edit: added the data step around the do loop.

Re: How can I expand a dataset into a panel?

Reeza — Sun, 24 Jun 2018 19:35:14 GMT

PROC TIMESERIES, PROC EXPAND can be used to prep time series data, but a manual data step or SQL join is just as efficient and simple for your use case.

@xyxu wrote:

I have a dataset as follows.

variable: ID

obs1: A

obs2: B

obs3: C

I hope to expand this dataset to a panel, where each ID corresponds to a time series:

variable: ID year

obs1: A 2001

obs2: A 2002

obs3: A 2003

obs4: B 2001

obs5: B 2002

obs6: B 2003

obs7: C 2001

obs8: C 2002

obs9: C 2003

Which command can perform this expasion?

Re: How can I expand a dataset into a panel?

xyxu — Sun, 24 Jun 2018 20:33:30 GMT

Could you provide a sample code of SQL join for this purpose? I am not familiar with SQL. Thanks!

Re: How can I expand a dataset into a panel?

Astounding — Sun, 24 Jun 2018 23:13:38 GMT

You already have a data set with the list of ID values. Create a similar data set with the list of YEAR values:

data year_list;

do year = 2001 to 2003;

output;

end;

run;

Then SQL will give you all combinations:

data want;

select * from id_list, year_list;

quit;

As was suggested earlier, you could just use a DATA step with your existing list of IDs. If you really don't know SQL at this point, I'm not sure that this SQL solution is tremendously helpful. It might be better to build upon what you already know.