Hi Everyone,
I have the following data:
data have;
input Sedol Date:ddmmyy10. Buy_volume Price;
format Date yymmdd10.;
cards;
2000001 2/01/1995 196000 0
2000001 10/02/1995 . .
2000001 27/02/1995 36000 4.55
2000001 2/03/1995 60000 4.31
2000001 13/03/1995 120000 4.51
2000002 2/01/1995 294000 0
2000002 10/02/1995 . .
2000002 27/02/1995 . .
2000002 28/02/1995 50000 14.2
2000002 13/03/1995 140000 11.6
2000002 20/03/1995 40000 11.6
2000002 22/03/1995 . .
2000003 2/01/1995 150000 0
2000003 28/02/1995 10000 13.6
2000003 24/05/1995 6000 13.5
2000003 5/06/1995 . .
2000003 18/07/1995 . .
2000003 25/02/1998 40000 30
2000003 6/04/1998 44000 31.88
;
For each Sedol, I need to fill up the first line in Price column with the first available data in the same column. Basically this is what I want:
Sedol | Date | Buy_volume | Price |
2000001 | 2/01/1995 | 196000 | 4.55 |
2000001 | 10/02/1995 | . | . |
2000001 | 27/02/1995 | 36000 | 4.55 |
2000001 | 2/03/1995 | 60000 | 4.31 |
2000001 | 13/03/1995 | 120000 | 4.51 |
2000002 | 2/01/1995 | 294000 | 14.2 |
2000002 | 10/02/1995 | . | . |
2000002 | 27/02/1995 | . | . |
2000002 | 28/02/1995 | 50000 | 14.2 |
2000002 | 13/03/1995 | 140000 | 11.6 |
2000002 | 20/03/1995 | 40000 | 11.6 |
2000002 | 22/03/1995 | . | . |
2000003 | 2/01/1995 | 150000 | 13.6 |
2000003 | 28/02/1995 | 10000 | 13.6 |
2000003 | 24/05/1995 | 6000 | 13.5 |
2000003 | 5/06/1995 | . | . |
2000003 | 18/07/1995 | . | . |
2000003 | 25/02/1998 | 40000 | 30 |
2000003 | 6/04/1998 | 44000 | 31.88 |
Can someone please help? Thanks.
If you have SAS ETS, I believe PROC EXPAND can do this, otherwise here's relatively cumbersome approach.
data have;
input Sedol Date:ddmmyy10. Buy_volume Price;
format Date yymmdd10.;
cards;
2000001 2/01/1995 196000 0
2000001 10/02/1995 . .
2000001 27/02/1995 36000 4.55
2000001 2/03/1995 60000 4.31
2000001 13/03/1995 120000 4.51
2000002 2/01/1995 294000 0
2000002 10/02/1995 . .
2000002 27/02/1995 . .
2000002 28/02/1995 50000 14.2
2000002 13/03/1995 140000 11.6
2000002 20/03/1995 40000 11.6
2000002 22/03/1995 . .
2000003 2/01/1995 150000 0
2000003 28/02/1995 10000 13.6
2000003 24/05/1995 6000 13.5
2000003 5/06/1995 . .
2000003 18/07/1995 . .
2000003 25/02/1998 40000 30
2000003 6/04/1998 44000 31.88
;
data temp;
set have
/*removes missing and price=0 records*/ (where =(not missing(price) and price ne 0));;
by sedol;
keep sedol price;
rename price = first_price;
*keeps only first for each sedol;
if first.sedol ;
run;
data want;
merge have (in=a)
temp;
by sedol;
if a; *only if in Table Have;
if first.sedol and price=0 then price=first_price; *replace if necesssary;
drop first_price; *drop first value;
run;
@bd_user_10 wrote:
Hi Everyone,
I have the following data:
data have;
input Sedol Date:ddmmyy10. Buy_volume Price;
format Date yymmdd10.;
cards;
2000001 2/01/1995 196000 0
2000001 10/02/1995 . .
2000001 27/02/1995 36000 4.55
2000001 2/03/1995 60000 4.31
2000001 13/03/1995 120000 4.51
2000002 2/01/1995 294000 0
2000002 10/02/1995 . .
2000002 27/02/1995 . .
2000002 28/02/1995 50000 14.2
2000002 13/03/1995 140000 11.6
2000002 20/03/1995 40000 11.6
2000002 22/03/1995 . .
2000003 2/01/1995 150000 0
2000003 28/02/1995 10000 13.6
2000003 24/05/1995 6000 13.5
2000003 5/06/1995 . .
2000003 18/07/1995 . .
2000003 25/02/1998 40000 30
2000003 6/04/1998 44000 31.88
;
For each Sedol, I need to fill up the first line in Price column with the first available data in the same column. Basically this is what I want:
Sedol Date Buy_volume Price 2000001 2/01/1995 196000 4.55 2000001 10/02/1995 . . 2000001 27/02/1995 36000 4.55 2000001 2/03/1995 60000 4.31 2000001 13/03/1995 120000 4.51 2000002 2/01/1995 294000 14.2 2000002 10/02/1995 . . 2000002 27/02/1995 . . 2000002 28/02/1995 50000 14.2 2000002 13/03/1995 140000 11.6 2000002 20/03/1995 40000 11.6 2000002 22/03/1995 . . 2000003 2/01/1995 150000 13.6 2000003 28/02/1995 10000 13.6 2000003 24/05/1995 6000 13.5 2000003 5/06/1995 . . 2000003 18/07/1995 . . 2000003 25/02/1998 40000 30 2000003 6/04/1998 44000 31.88
Can someone please help? Thanks.
If you have SAS ETS, I believe PROC EXPAND can do this, otherwise here's relatively cumbersome approach.
data have;
input Sedol Date:ddmmyy10. Buy_volume Price;
format Date yymmdd10.;
cards;
2000001 2/01/1995 196000 0
2000001 10/02/1995 . .
2000001 27/02/1995 36000 4.55
2000001 2/03/1995 60000 4.31
2000001 13/03/1995 120000 4.51
2000002 2/01/1995 294000 0
2000002 10/02/1995 . .
2000002 27/02/1995 . .
2000002 28/02/1995 50000 14.2
2000002 13/03/1995 140000 11.6
2000002 20/03/1995 40000 11.6
2000002 22/03/1995 . .
2000003 2/01/1995 150000 0
2000003 28/02/1995 10000 13.6
2000003 24/05/1995 6000 13.5
2000003 5/06/1995 . .
2000003 18/07/1995 . .
2000003 25/02/1998 40000 30
2000003 6/04/1998 44000 31.88
;
data temp;
set have
/*removes missing and price=0 records*/ (where =(not missing(price) and price ne 0));;
by sedol;
keep sedol price;
rename price = first_price;
*keeps only first for each sedol;
if first.sedol ;
run;
data want;
merge have (in=a)
temp;
by sedol;
if a; *only if in Table Have;
if first.sedol and price=0 then price=first_price; *replace if necesssary;
drop first_price; *drop first value;
run;
@bd_user_10 wrote:
Hi Everyone,
I have the following data:
data have;
input Sedol Date:ddmmyy10. Buy_volume Price;
format Date yymmdd10.;
cards;
2000001 2/01/1995 196000 0
2000001 10/02/1995 . .
2000001 27/02/1995 36000 4.55
2000001 2/03/1995 60000 4.31
2000001 13/03/1995 120000 4.51
2000002 2/01/1995 294000 0
2000002 10/02/1995 . .
2000002 27/02/1995 . .
2000002 28/02/1995 50000 14.2
2000002 13/03/1995 140000 11.6
2000002 20/03/1995 40000 11.6
2000002 22/03/1995 . .
2000003 2/01/1995 150000 0
2000003 28/02/1995 10000 13.6
2000003 24/05/1995 6000 13.5
2000003 5/06/1995 . .
2000003 18/07/1995 . .
2000003 25/02/1998 40000 30
2000003 6/04/1998 44000 31.88
;
For each Sedol, I need to fill up the first line in Price column with the first available data in the same column. Basically this is what I want:
Sedol Date Buy_volume Price 2000001 2/01/1995 196000 4.55 2000001 10/02/1995 . . 2000001 27/02/1995 36000 4.55 2000001 2/03/1995 60000 4.31 2000001 13/03/1995 120000 4.51 2000002 2/01/1995 294000 14.2 2000002 10/02/1995 . . 2000002 27/02/1995 . . 2000002 28/02/1995 50000 14.2 2000002 13/03/1995 140000 11.6 2000002 20/03/1995 40000 11.6 2000002 22/03/1995 . . 2000003 2/01/1995 150000 13.6 2000003 28/02/1995 10000 13.6 2000003 24/05/1995 6000 13.5 2000003 5/06/1995 . . 2000003 18/07/1995 . . 2000003 25/02/1998 40000 30 2000003 6/04/1998 44000 31.88
Can someone please help? Thanks.
data have;
input Sedol Date:ddmmyy10. Buy_volume Price;
format Date yymmdd10.;
cards;
2000001 2/01/1995 196000 0
2000001 10/02/1995 . .
2000001 27/02/1995 36000 4.55
2000001 2/03/1995 60000 4.31
2000001 13/03/1995 120000 4.51
2000002 2/01/1995 294000 0
2000002 10/02/1995 . .
2000002 27/02/1995 . .
2000002 28/02/1995 50000 14.2
2000002 13/03/1995 140000 11.6
2000002 20/03/1995 40000 11.6
2000002 22/03/1995 . .
2000003 2/01/1995 150000 0
2000003 28/02/1995 10000 13.6
2000003 24/05/1995 6000 13.5
2000003 5/06/1995 . .
2000003 18/07/1995 . .
2000003 25/02/1998 40000 30
2000003 6/04/1998 44000 31.88
;
proc sql;
create table want as
select a.sedol,a.date,Buy_volume, ifn(min(date)=date,p,price) as Price
from
have a left join
(select sedol, price as p from have where price not in (0,.) group by sedol having min(date)=date) b
on a.sedol=b.sedol
group by a.sedol
order by sedol,date;
quit;
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.