DATA Step, Macro, Functions and more

How can I expand a dataset into a panel?

Reply
Occasional Contributor
Posts: 15

How can I expand a dataset into a panel?

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?

 

 

Super User
Posts: 10,283

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

[ Edited ]
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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 23,778

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

[ Edited ]

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?

 

 


 

Occasional Contributor
Posts: 15

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

Could you provide a sample code of SQL join for this purpose? I am not familiar with SQL. Thanks!
Super User
Posts: 6,785

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

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.

Ask a Question
Discussion stats
  • 4 replies
  • 82 views
  • 0 likes
  • 4 in conversation