topic Re: How to fill up the first line in a column with the first available data in the same column? in SAS Programming

How to fill up the first line in a column with the first available data in the same column?

bd_user_10 — Wed, 09 Jan 2019 03:29:47 GMT

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.

Re: How to fill up the first line in a column with the first available data in the same column?

Reeza — Wed, 09 Jan 2019 04:05:44 GMT

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.

Re: How to fill up the first line in a column with the first available data in the same column?

novinosrin — Wed, 09 Jan 2019 05:44:15 GMT



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;

Re: How to fill up the first line in a column with the first available data in the same column?

bd_user_10 — Wed, 09 Jan 2019 06:24:46 GMT

Thanks for the codes. It works perfectly!