Filling missing values

[Costasg]

Hello all,

I have a database and I need to fill some missing values. Particullarly, the database spans from the 1980s till 2012. Some information has been available after 1998. So what I want to do is to fill the missing values for these variables back to the start date. More specifically, I want to update the following variables:

-Country

-Exchange Code

-Stock Class Code

-Stock Class Description

-Industry Code

Can anyone please help me on this?

I am attaching a small sample of the database for 1 stock (as the whole database exceeds 10gb).

Many thanks in advance

[PGStats]

What date of the year do you want for the missing years? Which is the date, RDATE or FDATE?

[Costasg]

Hi PGStats,

I want to fill these variables from the start date of the database (31/12/1980) and for every observation.

So COUNTRY variable should be filled according to manager number and RDATE

and  Exchange Code, Stock Class Code, Stock Class Description and Industry Code variables should be filled according to ticker symbol and FDATE

[Ankitsas]

Hi Costasg,

Can you please elaborate on the statement "So COUNTRY variable should be filled according to manager number and RDATE" ?i was trying to work around on this, but the sentence sounds little noisy so thought to ask you.

Thanks,

Ankit

[Costasg]

Actually I don't think that dates should matter. There are different dates as these are seperate files downloaded together.

So, the only thing that needed to be done is to fill the variable Country based on the manager number

and the variables  Exchange Code, Stock Class Code, Stock Class Description and Industry Code based on the ticker symbol.

I hope the above helps.

Thanks,

Costasg

[Ankitsas]

Hi Costasg,

Below is the code which should help you to serve your purpose -

data test;

set tmp1.air;

run;

/*****filling country value*****/

proc sort data=test out=test_sorted;

by mgrno descending country;

run;

data step1(drop=country rename=(country_new=country));

set test_sorted;

by mgrno ;

retain country_new;

if first.mgrno then country_new=country;

run;

/****filling  Exchange Code, Stock Class Code, Stock Class Description and Industry Code***/

proc sort data=step1 out=step1_sorted;

by ticker descending EXCHCD descending STKCD descending STKCDESC descending INDCODE;

run;

data step2(drop=EXCHCD STKCD STKCDESC INDCODE rename=(exchcd_new=EXCHCD STKCD_new=STKCD STKCDESC_new=STKCDESC INDCODE_new=INDCODE));

set step1_sorted;

by ticker;

if first.ticker then do;

exchcd_new=EXCHCD;

STKCD_new=STKCD;

STKCDESC_new=STKCDESC;

INDCODE_new=INDCODE;

end;

run;

Let me know in case of any queries

Thanks,

Ankit

[Costasg]

Hi ankitsas,

many thanks for this; however it only works for the country variable. The other variables are left missing (including those that initially had values)

[Ankitsas · Accepted solution]

Hi Costasg,

Sincere apology, please find the updated code below :-

data test;

set tmp1.air;

run;

/*****filling country value*****/

proc sort data=test out=test_sorted;

by mgrno descending country;

run;

data step1(drop=country rename=(country_new=country));

set test_sorted;

by mgrno ;

retain country_new;

if first.mgrno then country_new=country;

run;

/****filling  Exchange Code, Stock Class Code, Stock Class Description and Industry Code***/

proc sort data=step1 out=step1_sorted;

by ticker descending EXCHCD descending STKCD descending STKCDESC descending INDCODE;

run;

data step2(drop=EXCHCD STKCD STKCDESC INDCODE rename=(exchcd_new=EXCHCD STKCD_new=STKCD STKCDESC_new=STKCDESC INDCODE_new=INDCODE));

set step1_sorted;

by ticker;

retain exchcd_new STKCD_new STKCDESC_new INDCODE_new;

if first.ticker then do;

exchcd_new=EXCHCD;

STKCD_new=STKCD;

STKCDESC_new=STKCDESC;

INDCODE_new=INDCODE;

end;

run;

Please use this code and hope it will work

Thanks,

Ankit

[Costasg]

It works just fine!

Many thanks Ankit!