BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mmmark
Fluorite | Level 6

I'm searching for a way to convert quarterly data:

Here's a sample. I have about 150 companies with quarterly data from 2002-2012.

Ticker          date               Price    

AAA         2002/03/31          100

AAA         2002/06/30          125

AAA         2002/09/30          100

AAA         2002/12/31          100

AAA         2003/03/31          102

....

AAA          2012/12/31          95

ABB         2002/03/31          100

ABB         2002/06/30          125

ABB         2002/09/30          110

ABB         2002/12/31          100

ABB         2003/03/31          102

....

ABB          2012/12/31          99

The two problems I'm facing are

1. How to incorporate the ticker, and the fact that we're extrapolating/expanding for mutliple companies.

2. The dates are quarterly end, rather than at the start.

How can I expand this data to monthly or even daily?

1 ACCEPTED SOLUTION

Accepted Solutions
mmmark
Fluorite | Level 6

Just figured it out, it was the simplest thing!

A BY statement can be used with PROC EXPAND to obtain separate analyses on observations in groups defined by the BY variables. The input data set must be sorted by the BY variables and be sorted by the ID variable within each BY group.

Use a BY statement when you want to interpolate or convert time series within levels of a cross-sectional variable. For example, suppose you have a data set STATE containing annual estimates of average disposable personal income per capita (DPI) by state and you want quarterly estimates by state. These statements convert the DPI series within each state:

   proc sort data=state;

      by state date;

   run;

   proc expand data=state out=stateqtr from=year to=qtr;

      convert dpi;

      by state;

      id date;

   run;

View solution in original post

6 REPLIES 6
PGStats
Opal | Level 21

If you have SAS/ETS licensed, PROC EXPAND does exactly that. - PG

PG
mmmark
Fluorite | Level 6

The two problems I'm facing are

1. How to incorporate the ticker, and the fact that we're extrapolating/expanding for mutliple companies.

2. The dates are quarterly end, rather than at the start.

I have SAS basic and EG.

mmmark
Fluorite | Level 6

Since the ID statement cannot handle anything other than a date value and you can't have one date appear more than once (which each date does for each company), how can I repeat the PROC EXPAND process for each company?

mmmark
Fluorite | Level 6

Right now what I can think of is perhaps creating a separate dataset for each company, apply PROC EXPAND, then combine the datasets. This would involve hundreds of datasets however....

mmmark
Fluorite | Level 6

Just figured it out, it was the simplest thing!

A BY statement can be used with PROC EXPAND to obtain separate analyses on observations in groups defined by the BY variables. The input data set must be sorted by the BY variables and be sorted by the ID variable within each BY group.

Use a BY statement when you want to interpolate or convert time series within levels of a cross-sectional variable. For example, suppose you have a data set STATE containing annual estimates of average disposable personal income per capita (DPI) by state and you want quarterly estimates by state. These statements convert the DPI series within each state:

   proc sort data=state;

      by state date;

   run;

   proc expand data=state out=stateqtr from=year to=qtr;

      convert dpi;

      by state;

      id date;

   run;

PGStats
Opal | Level 21

Great! You should be able to answer your initial request now. - PG

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 4329 views
  • 1 like
  • 2 in conversation