DATA Step, Macro, Functions and more

need some help using proc expand

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

need some help using proc expand

dear forum,

i am a new student learning with SAS,

i got stuck at one thing for like a week and still cant figure out what to do to achieve the thing i want...

 

the beginning data set looks like this

StockID       Date              BV

A               1999Q1           202

A               2000Q2           308

A               2001Q3           405

B               1995Q2           507

B               1998Q1           609

B               1999Q4           201

 

the data i want looks like this

StockID        Date              BV

A               19990101        308

A               19990102        308

A               19990103        308

A               19990104        308

A                .                      .

A                .                      .

A                .                      .

A               20000531        308

A               20000601        405

A               20000602        405

A               .                       .

A               .                       .

B              19950601         609

B              19950602         609

B              .                        .

B              .                        .

B              19971231        609

B              19980101        201

B              19980102        201

 

 


Accepted Solutions
Solution
‎04-10-2016 10:15 PM
Super User
Posts: 9,681

Re: need some help using proc expand

But

A               20000531        308

A               20000601        405  <--- It is not 2000Q2          

A               20000602        405

 

 

data have;
input StockID   $    Date    : yyq6.         BV;
format Date yyq6.;
cards;
A               1999Q1           202
A               2000Q2           308
A               2001Q3           405
B               1995Q2           507
B               1998Q1           609
B               1999Q4           201
;
run;

data want;
 merge have(drop=BV) 
 have(firstobs=2  rename=(StockID=_StockID Date=_Date BV=_BV));
 if StockID=_StockID then do;
  do i=Date to _Date-1;
   new_Date=i;BV=_BV;output;
  end;
 end;
 drop i _: Date ;
 format new_Date yymmddn8.;
run;

View solution in original post


All Replies
Super User
Posts: 17,840

Re: need some help using proc expand

You want missing dates? What's the rule/logic here?

Occasional Contributor
Posts: 8

Re: need some help using proc expand

the data come in irregular periods I want to convert them into regular daily obs showing the next(lead) BV value
Super User
Posts: 17,840

Re: need some help using proc expand

Ok. What does your code look like so far?
And what is regular daily - every day or every day the stock market is open?
What is the rule for the time period, is it over a fixed period or depends on the data somehow? Is it same for all stocks?
What do the missing dates in your output data set represent, or were they intended more as a continues on like this indicator?
Last but not least - are you set on using proc expand?
Occasional Contributor
Posts: 8

Re: need some help using proc expand

proc expand data=BV out=temp to=day;
id date;

by code;
convert BV / observed=(beginning,end);
run;

 

 

the source data were in irregular periods for all the stockIDs

eg stock A might have BV at 1999Q1 and the next ob could be 2008Q2; while stock B might have a BV at 2005Q2 and the next ob for stock B might be 2009Q3...

and i want stock A have the same BV from 1st day of 1999Q1(19990101) to the last day of 2008Q2(20080630) have the same BV as in 2008Q2...same for stock B to have the same BV from 1st day of 2005Q2(20050301) to the last day of 2009Q3(200909030)...

 

and thanks so much for the reply

Super User
Posts: 17,840

Re: need some help using proc expand


ygity wrote:

 

 i want stock A have the same BV from 1st day of 1999Q1(19990101) to the last day of 2008Q2(20080630) have the same BV as in 2008Q2...same for stock B to have the same BV from 1st day of 2005Q2(20050301) to the last day of 2009Q3(200909030)...

 


How do you know you want that? Is it based on data in the tables, ie first observed record to last observed record?

Solution
‎04-10-2016 10:15 PM
Super User
Posts: 9,681

Re: need some help using proc expand

But

A               20000531        308

A               20000601        405  <--- It is not 2000Q2          

A               20000602        405

 

 

data have;
input StockID   $    Date    : yyq6.         BV;
format Date yyq6.;
cards;
A               1999Q1           202
A               2000Q2           308
A               2001Q3           405
B               1995Q2           507
B               1998Q1           609
B               1999Q4           201
;
run;

data want;
 merge have(drop=BV) 
 have(firstobs=2  rename=(StockID=_StockID Date=_Date BV=_BV));
 if StockID=_StockID then do;
  do i=Date to _Date-1;
   new_Date=i;BV=_BV;output;
  end;
 end;
 drop i _: Date ;
 format new_Date yymmddn8.;
run;
Respected Advisor
Posts: 4,651

Re: need some help using proc expand

I don't think you can get exactly what you want from proc expand. Try this instead

 

data have;
input StockID $ Date :yyq.  BV;
format date yymmdd10.;
datalines;
A               1999Q1           202
A               2000Q2           308
A               2001Q3           405
B               1995Q2           507
B               1998Q1           609
B               1999Q4           201
;

data want;
set have;
by stockid;
pDate = lag(date);
if first.stockId then do;
    newDate = date;
    output;
    end;
else
    do newDate = intnx("DAY", pDate, 1) to date;
        output;
        end;
keep stockId newDate BV;
rename newDate=date;
format newDate yymmdd10.;
run;
PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 290 views
  • 1 like
  • 4 in conversation