topic Re: Filling missing values in SAS Procedures

Filling missing values

Costasg — Sat, 02 Feb 2013 16:36:32 GMT

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

Re: Filling missing values

PGStats — Sat, 02 Feb 2013 16:51:27 GMT

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

Re: Filling missing values

Costasg — Sat, 02 Feb 2013 17:33:34 GMT

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

Re: Filling missing values

Ankitsas — Sun, 03 Feb 2013 13:24:09 GMT

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

Re: Filling missing values

Costasg — Sun, 03 Feb 2013 13:32:09 GMT

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

Re: Filling missing values

Ankitsas — Sun, 03 Feb 2013 14:34:35 GMT

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

Re: Filling missing values

Costasg — Sun, 03 Feb 2013 14:45:37 GMT

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)

Re: Filling missing values

Ankitsas — Sun, 03 Feb 2013 15:04:12 GMT

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

Re: Filling missing values

Costasg — Sun, 03 Feb 2013 21:59:49 GMT

It works just fine!

Many thanks Ankit!