topic Re: need some help using proc expand in SAS Programming

need some help using proc expand

ygity — Sat, 19 Mar 2016 11:26:00 GMT

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 20000531 308

A 20000601 405

A 20000602 405

A . .

B 19950601 609

B 19950602 609

B . .

B 19971231 609

B 19980101 201

B 19980102 201

Re: need some help using proc expand

Reeza — Sat, 19 Mar 2016 23:16:41 GMT

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

Re: need some help using proc expand

ygity — Sun, 20 Mar 2016 00:07:09 GMT

the data come in irregular periods I want to convert them into regular daily obs showing the next(lead) BV value

Re: need some help using proc expand

Reeza — Sun, 20 Mar 2016 00:20:51 GMT

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?

Re: need some help using proc expand

Ksharp — Sun, 20 Mar 2016 02:44:02 GMT

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;

Re: need some help using proc expand

ygity — Sun, 20 Mar 2016 02:51:25 GMT

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

Re: need some help using proc expand

Reeza — Sun, 20 Mar 2016 02:53:43 GMT

@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?

Re: need some help using proc expand

PGStats — Sun, 20 Mar 2016 03:43:36 GMT

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;