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
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;
You want missing dates? What's the rule/logic here?
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
@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?
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.