BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Costasg
Calcite | Level 5


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

1 ACCEPTED SOLUTION

Accepted Solutions
Ankitsas
Calcite | Level 5

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

8 REPLIES 8
PGStats
Opal | Level 21

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

PG
Costasg
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

It works just fine!

Many thanks Ankit!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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