BookmarkSubscribeRSS Feed
xyxu
Quartz | Level 8

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?

 

 

4 REPLIES 4
Kurt_Bremser
Super User
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.

Reeza
Super User

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?

 

 


 

xyxu
Quartz | Level 8
Could you provide a sample code of SQL join for this purpose? I am not familiar with SQL. Thanks!
Astounding
PROC Star

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1418 views
  • 0 likes
  • 4 in conversation