create variable with multiple conditional

[anzas rustamaji pratama]

Dear STATA user

I need help to generate new variable "grade" that contain observation "investment" and "non-investment". The criteria which observation is "investment" or "non-investment" are:
--> “1” or “investment” if observation in variable “cur_rating” and "last_rating" is one of this letter:

agency	rating
Moody's	Aaa	Aa1	Aa2	Aa3	A1	A2	A3	Baa1	Baa2	Baa3
Standard & Poor's (S&P)	AAA	AA+	AA	AA-	A+	A	A-	BBB+	BBB	BBB-
Fitch	AAA	AA+	AA	AA-	A+	A	A-	BBB+	BBB	BBB-
PEFINDO	idAAA	idAA+	idAA	idAA-	idA+	idA	idA-	idBBB+	idBBB	idBBB-

--> “0” or “non-investment” if observation in variable “cur_rating” and "last_rating" is one of this letter.:

agency

rating

Moody's

Ba1

Ba2

Ba3

B1

B2

B3

Caa1

Caa2

Caa3

Ca

C

Standard & Poor's (S&P)

BB+

BB

BB-

B+

B

B-

CCC+

CCC

CCC-

CC

C

D

Fitch

BB+

BB

BB-

B+

B

B-

CCC+

CCC

CCC-

CC

C

D

PEFINDO

idBB+

idBB

idBB-

idB+

idB

idB-

idCCC

idD

idSD

* Example generated by -dataex-. To install: ssc install dataex
clear
input float event_date long(firm_id agency cur_rating last_rating)
14641 100 3 37 43
14651 131 3 28 35
14651  32 3 28 35
14651 184 3 28 35
14651  21 3 28 35
14742  85 3 42 48
14753  30 3 29 33
14777  40 4 22 30
14791 137 3 27 47
14812 169 3 27 48
14812  87 3 27 47
14812 149 3 27 48
14812  25 4 23 20
14823 116 3 41 42
14850 107 3 36 40
14853 124 3 29 33
14868  75 3 35 47
14868 157 3 27 41
14894  81 3 27 47
14908 146 3 27 48
14913 168 3 27 48
14914 100 3 41 42
14914 156 3 39 46
14914  27 3 36 39
14956 116 3 39 46
14958 113 4 22 30
14963 163 2 27 25
14966  29 3 28 35
14984 189 3 40 44
14994  40 4 23 26
15008  26 4 22 30
15013 184 4 25 26
15020  21 3 37 33
15020  78 4 21 28
15020 184 3 37 33
15020  32 3 37 33
15020 131 3 37 33
15046  99 3 43 32
15047 184 3 42 42
15047  32 3 42 42
15047 131 3 42 42
15047  21 3 42 42
15062  89 3 39 46
15073 113 4 25 26
15074 113 4 23 30
15079 131 3 43 47
15081  32 3 43 47
15091  21 3 43 47
15103 184 3 43 47
15103   8 3 43 39
15124  85 3 38 47
15151 132 4 23 26
15151  26 4 23 26
15151 121 4 23 26
15151 163 4 23 26
15151 186 4 23 26
15174 110 3 27 48
15188 124 4  1 21
15201  27 3 37 41
15251  81 3 43 32
15278 152 4 20 21
15279 111 3 27 47
15279 167 3 27 48
15280 116 3 41 44
15281  92 3 27 35
15285 124 3 33 34
15340 107 3 38 41
15340  97 3 27 48
15371 107 3 42 43
15399 107 3 44 47
15400 116 3 38 46
15431 116 3 42 43
15431  33 3 28 35
15447 116 3 43 47
15488  92 3 30 32
15491 174 3 29 33
15540  85 3 27 43
15552 116 3 35 48
15554 156 1  1  4
15554  79 1  1  4
15554  93 1  1  4
15558 182 1  1  4
15574 174 3 44 34
15574  22 4 19 23
15579  27 3 41 42
15582 161 3 38 39
15607 100 3 39 46
15608 156 3 40 44
15614 103 3 27 45
15627  30 3 33 34
15631  22 4 23 21
15638  75 3 42 40
15676 124 3 31 38
15718  75 3 35 47
15747 194 3 41 32
15776  67 3 28 35
15790 162 3 40 44
15797  33 3 29 33
15803  99 3 27 48
15803 135 3 27 48
end
format %td event_date
label values firm_id firm_id
label def firm_id 8 "0781793DIJ Equity", modify
label def firm_id 21 "1014Z IJ Equity", modify
label def firm_id 22 "1021Z IJ Equity", modify
label def firm_id 25 "1046Z IJ Equity", modify
label def firm_id 26 "1050Z IJ Equity", modify
label def firm_id 27 "1067Z IJ Equity", modify
label def firm_id 29 "1070Z IJ Equity", modify
label def firm_id 30 "1072Z IJ Equity", modify
label def firm_id 32 "1085Z IJ Equity", modify
label def firm_id 33 "1111Z IJ Equity", modify
label def firm_id 40 "147674Z IN Equity", modify
label def firm_id 67 "AALI IJ Equity", modify
label def firm_id 75 "ASII IJ Equity", modify
label def firm_id 78 "BBCA IJ Equity", modify
label def firm_id 79 "BBIA IJ Equity", modify
label def firm_id 81 "BBLD IJ Equity", modify
label def firm_id 85 "BBTN IJ Equity", modify
label def firm_id 87 "BFIN IJ Equity", modify
label def firm_id 89 "BJBR IJ Equity", modify
label def firm_id 92 "BLTA IJ Equity", modify
label def firm_id 93 "BMRI IJ Equity", modify
label def firm_id 97 "BNPS IJ Equity", modify
label def firm_id 99 "BRPT IJ Equity", modify
label def firm_id 100 "BSBR IJ Equity", modify
label def firm_id 103 "BUDI IJ Equity", modify
label def firm_id 107 "CMNP IJ Equity", modify
label def firm_id 110 "CTRA IJ Equity", modify
label def firm_id 111 "DART IJ Equity", modify
label def firm_id 113 "DGSA IJ Equity", modify
label def firm_id 116 "DUTI IJ Equity", modify
label def firm_id 121 "FASW IJ Equity", modify
label def firm_id 124 "HMSP IJ Equity", modify
label def firm_id 131 "INKP IJ Equity", modify
label def firm_id 132 "INRU IJ Equity", modify
label def firm_id 135 "JIHD IJ Equity", modify
label def firm_id 137 "JRPT IJ Equity", modify
label def firm_id 146 "MDBN IJ Equity", modify
label def firm_id 149 "MLND IJ Equity", modify
label def firm_id 152 "MPPA IJ Equity", modify
label def firm_id 156 "NISP IJ Equity", modify
label def firm_id 157 "PAFI IJ Equity", modify
label def firm_id 161 "PLN IJ Equity", modify
label def firm_id 162 "PNBN IJ Equity", modify
label def firm_id 163 "POLY IJ Equity", modify
label def firm_id 167 "PUDP IJ Equity", modify
label def firm_id 168 "PWON IJ Equity", modify
label def firm_id 169 "PWSI IJ Equity", modify
label def firm_id 174 "SMGR IJ Equity", modify
label def firm_id 182 "TELZ IJ Equity", modify
label def firm_id 184 "TKIM IJ Equity", modify
label def firm_id 186 "TPIA IJ Equity", modify
label def firm_id 189 "ULTJ IJ Equity", modify
label def firm_id 194 "WIKA IJ Equity", modify
label values agency credit_agency
label def credit_agency 1 "Fitch", modify
label def credit_agency 2 "Moody's", modify
label def credit_agency 3 "PEFIN", modify
label def credit_agency 4 "S&P", modify
label values cur_rating cur_rating
label def cur_rating 1 "B", modify
label def cur_rating 19 "CCC", modify
label def cur_rating 20 "CCC+", modify
label def cur_rating 21 "CCCpi", modify
label def cur_rating 22 "D", modify
label def cur_rating 23 "NR", modify
label def cur_rating 25 "SD", modify
label def cur_rating 27 "WR", modify
label def cur_rating 28 "idA", modify
label def cur_rating 29 "idA+", modify
label def cur_rating 30 "idA-", modify
label def cur_rating 31 "idAA", modify
label def cur_rating 33 "idAA-", modify
label def cur_rating 35 "idB+", modify
label def cur_rating 36 "idBB", modify
label def cur_rating 37 "idBB+", modify
label def cur_rating 38 "idBB-", modify
label def cur_rating 39 "idBBB", modify
label def cur_rating 40 "idBBB+", modify
label def cur_rating 41 "idBBB-", modify
label def cur_rating 42 "idCCC", modify
label def cur_rating 43 "idD", modify
label def cur_rating 44 "idSD", modify
label values last_rating last_rating
label def last_rating 4 "B-", modify
label def last_rating 20 "CC", modify
label def last_rating 21 "CCC", modify
label def last_rating 23 "CCC-", modify
label def last_rating 25 "Ca", modify
label def last_rating 26 "D", modify
label def last_rating 28 "R", modify
label def last_rating 30 "SD", modify
label def last_rating 32 "WR", modify
label def last_rating 33 "idA", modify
label def last_rating 34 "idA+", modify
label def last_rating 35 "idA-", modify
label def last_rating 38 "idAA-", modify
label def last_rating 39 "idB", modify
label def last_rating 40 "idB+", modify
label def last_rating 41 "idBB", modify
label def last_rating 42 "idBB+", modify
label def last_rating 43 "idBB-", modify
label def last_rating 44 "idBBB", modify
label def last_rating 45 "idBBB+", modify
label def last_rating 46 "idBBB-", modify
label def last_rating 47 "idCCC", modify
label def last_rating 48 "idD", modify

Thanks in advance for your help

regards,
Anzas

[Daniel Bela]

Maybe Stata's concept of value label dereference helps here, although my feeling is that it is seldom used; anyways, here is a code example that does half of the trick:

generate investment=.
// investments:
replace investment=1 if ///
    agency=="Moody's":credit_agency & inlist(cur_rating,"Aaa":cur_rating,"Aa1":cur_rating,"Aa2":cur_rating,"Aa3":cur_rating,"A1":cur_rating,"A2":cur_rating,"A3":cur_rating,"Baa1":cur_rating,"Baa2":cur_rating,"Baa3":cur_rating) | ///
    agency=="S&P":credit_agency & inlist(cur_rating,"AAA":cur_rating,"AA+":cur_rating,"AA":cur_rating,"AA-":cur_rating,"A+":cur_rating,"A":cur_rating,"A-":cur_rating,"BBB+":cur_rating,"BBB":cur_rating,"BBB-":cur_rating) | ///
    agency=="Fitch":credit_agency & inlist(cur_rating,"AAA":cur_rating,"AA+":cur_rating,"AA":cur_rating,"AA-":cur_rating,"A+":cur_rating,"A":cur_rating,"A-":cur_rating,"BBB+":cur_rating,"BBB":cur_rating,"BBB-":cur_rating) | ///
    agency=="PEFIN":credit_agency & inlist(cur_rating,"idAAA":cur_rating,"idAA+":cur_rating,"idAA":cur_rating,"idAA-":cur_rating,"idA+":cur_rating,"idA":cur_rating,"idA-":cur_rating,"idBBB+":cur_rating,"idBBB":cur_rating,"idBBB-":cur_rating)
// non-investments:
*replace investment=0 if ///
* ... TODO -- do the typing yourself

As Stata notes when running this code, your value labels are incomplete (for instance, "AAA" is not part of the value label). This may be an error, or may not.

Regards
Bela

[anzas rustamaji pratama]

thanks for the reply Daniel Bela .

when I run your command. The stata said that:

(value label dereference "Baa3":cur_rating not found)
(value label dereference "Baa2":cur_rating not found)
(value label dereference "Baa1":cur_rating not found)
(value label dereference "A3":cur_rating not found)
(value label dereference "A2":cur_rating not found)
(value label dereference "A1":cur_rating not found)
(value label dereference "Aa3":cur_rating not found)
(value label dereference "Aa2":cur_rating not found)
(value label dereference "Aa1":cur_rating not found)
(value label dereference "Aaa":cur_rating not found)

all my variable already in the numeric, do you have any suggestion to solve the problem?

thank you

[Robert Picard]

The first step is to create a mapping of each rating with its investment status. It's is a bit hard to explain how to do this since you presented the scheme in an html table instead of using dataex. What I did was to copy the content to the data editor, reshape it to a long layout, adjust the variable naming and save. This was done for both the investment and non-investment group. The resulting data can be loaded using:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str23 agency str6 rating float investment
"Fitch"                   "B"      0
"Fitch"                   "B+"     0
"Fitch"                   "B-"     0
"Fitch"                   "BB"     0
"Fitch"                   "BB+"    0
"Fitch"                   "BB-"    0
"Fitch"                   "C"      0
"Fitch"                   "CC"     0
"Fitch"                   "CCC"    0
"Fitch"                   "CCC+"   0
"Fitch"                   "CCC-"   0
"Fitch"                   "D"      0
"Fitch"                   "A"      1
"Fitch"                   "A+"     1
"Fitch"                   "A-"     1
"Fitch"                   "AA"     1
"Fitch"                   "AA+"    1
"Fitch"                   "AA-"    1
"Fitch"                   "AAA"    1
"Fitch"                   "BBB"    1
"Fitch"                   "BBB+"   1
"Fitch"                   "BBB-"   1
"Moody's"                 ""       0
"Moody's"                 "B1"     0
"Moody's"                 "B2"     0
"Moody's"                 "B3"     0
"Moody's"                 "Ba1"    0
"Moody's"                 "Ba2"    0
"Moody's"                 "Ba3"    0
"Moody's"                 "C"      0
"Moody's"                 "Ca"     0
"Moody's"                 "Caa1"   0
"Moody's"                 "Caa2"   0
"Moody's"                 "Caa3"   0
"Moody's"                 "A1"     1
"Moody's"                 "A2"     1
"Moody's"                 "A3"     1
"Moody's"                 "Aa1"    1
"Moody's"                 "Aa2"    1
"Moody's"                 "Aa3"    1
"Moody's"                 "Aaa"    1
"Moody's"                 "Baa1"   1
"Moody's"                 "Baa2"   1
"Moody's"                 "Baa3"   1
"PEFINDO"                 ""       0
"PEFINDO"                 ""       0
"PEFINDO"                 ""       0
"PEFINDO"                 "idB"    0
"PEFINDO"                 "idB+"   0
"PEFINDO"                 "idB-"   0
"PEFINDO"                 "idBB"   0
"PEFINDO"                 "idBB+"  0
"PEFINDO"                 "idBB-"  0
"PEFINDO"                 "idCCC"  0
"PEFINDO"                 "idD"    0
"PEFINDO"                 "idSD"   0
"PEFINDO"                 "idA"    1
"PEFINDO"                 "idA+"   1
"PEFINDO"                 "idA-"   1
"PEFINDO"                 "idAA"   1
"PEFINDO"                 "idAA+"  1
"PEFINDO"                 "idAA-"  1
"PEFINDO"                 "idAAA"  1
"PEFINDO"                 "idBBB"  1
"PEFINDO"                 "idBBB+" 1
"PEFINDO"                 "idBBB-" 1
"Standard & Poor's (S&P)" "B"      0
"Standard & Poor's (S&P)" "B+"     0
"Standard & Poor's (S&P)" "B-"     0
"Standard & Poor's (S&P)" "BB"     0
"Standard & Poor's (S&P)" "BB+"    0
"Standard & Poor's (S&P)" "BB-"    0
"Standard & Poor's (S&P)" "C"      0
"Standard & Poor's (S&P)" "CC"     0
"Standard & Poor's (S&P)" "CCC"    0
"Standard & Poor's (S&P)" "CCC+"   0
"Standard & Poor's (S&P)" "CCC-"   0
"Standard & Poor's (S&P)" "D"      0
"Standard & Poor's (S&P)" "A"      1
"Standard & Poor's (S&P)" "A+"     1
"Standard & Poor's (S&P)" "A-"     1
"Standard & Poor's (S&P)" "AA"     1
"Standard & Poor's (S&P)" "AA+"    1
"Standard & Poor's (S&P)" "AA-"    1
"Standard & Poor's (S&P)" "AAA"    1
"Standard & Poor's (S&P)" "BBB"    1
"Standard & Poor's (S&P)" "BBB+"   1
"Standard & Poor's (S&P)" "BBB-"   1
end
drop if mi(rating)
replace agency = "PEFIN" if agency == "PEFINDO"
replace agency = "S&P" if agency == "Standard & Poor's (S&P)"
save "agency_ratings.dta", replace

I don't know what lead you to encode your data but I think it is misguided. The following undoes the encoding and then merges the ratings with those saved above. There are some ratings that have no equivalent entry in the ratings data so the investment variable is missing for these cases.

rename (firm_id agency cur_rating last_rating) n_=

decode n_firm_id, gen(firm_id)
decode n_agency, gen(agency)
decode n_cur_rating, gen(cur_rating)
decode n_last_rating, gen(last_rating)
drop n_*

gen rating = cur_rating
merge m:1 agency rating using "agency_ratings.dta", keep(master match) nogen
rename investment cur_invest

replace rating = last_rating
merge m:1 agency rating using "agency_ratings.dta", keep(master match) nogen
rename investment last_invest
drop rating

sort event_date firm_id agency

[anzas rustamaji pratama]

dear Robert Picard

I have tried your solution but it didn't work

(note: variable agency was str7, now str23 to
       accommodate using data's values)

    Result                           # of obs.
    -----------------------------------------
    not matched                           784
        from master                       700  (_me
> rge==1)
        from using                         84  (_me
> rge==2)

    matched                                 0  (_me
> rge==3)
    -----------------------------------------

.

can you help me to figure out what's wrong with my dataset?

thanks

[Robert Picard]

Nope