Help using Base SAS procedures

Filling missing values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

Filling missing values


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

Attachment

Accepted Solutions
Solution
‎02-03-2013 10:04 AM
Occasional Contributor
Posts: 14

Re: Filling missing values

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

View solution in original post


All Replies
Respected Advisor
Posts: 4,932

Re: Filling missing values

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

PG
Frequent Contributor
Posts: 75

Re: Filling missing values

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

Occasional Contributor
Posts: 14

Re: Filling missing values

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

Frequent Contributor
Posts: 75

Re: Filling missing values

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

Occasional Contributor
Posts: 14

Re: Filling missing values

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

Frequent Contributor
Posts: 75

Re: Filling missing values

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)

Solution
‎02-03-2013 10:04 AM
Occasional Contributor
Posts: 14

Re: Filling missing values

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

Frequent Contributor
Posts: 75

Re: Filling missing values

It works just fine!

Many thanks Ankit!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 302 views
  • 0 likes
  • 3 in conversation