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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

7 REPLIES 7
Reeza
Super User

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

ygity
Calcite | Level 5
the data come in irregular periods I want to convert them into regular daily obs showing the next(lead) BV value
Reeza
Super User
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?
ygity
Calcite | Level 5

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

Reeza
Super User

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

Ksharp
Super User

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;
PGStats
Opal | Level 21

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

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
  • 7 replies
  • 1037 views
  • 1 like
  • 4 in conversation