Converting time variable from YY/MM to Months

[John Asherman]

Hi Guys,

I am struggling with converting a variable that contains values that represent prison sentences in the format yy/mm, i.e. 04/06 will be four years and six months. Any ideas how to generate a new var that contains the same values but expressed in months (so 02/05 would be 29 and so on)?

Thanks in advance

John

[Andrew Musau]

clear
input str5 sentence
"04/06"
"10/03"
"02/05"
end

gen wanted= (12*real(substr(sentence, 1, 2)))+ real(substr(sentence, -2, 2))

Res.:

. l

     +-------------------+
     | sentence   wanted |
     |-------------------|
  1. |    04/06       54 |
  2. |    10/03      123 |
  3. |    02/05       29 |
     +-------------------+

[Nick Cox]

Here's another way to do it:

clear
input str5 sentence
"04/06"
"10/03"
"02/05"
end

gen wanted = monthly(sentence, "YM", 2025) - ym(1999, 12)

list 

     +-------------------+
     | sentence   wanted |
     |-------------------|
  1. |    04/06       54 |
  2. |    10/03      123 |
  3. |    02/05       29 |
     +-------------------+

[John Asherman]

Thanks Nick! It seems to give me missing values in the wanted var though if the original values have 00 for MM. So any sentence with full years (e.g. 02/00) will be converted into . Any ideas how to fix this?

[Nick Cox]

Fair enough

clear
input str5 sentence
"04/06"
"10/03"
"02/05"
"02/00"
end


gen wanted = 12 * real(substr(sentence, 1, 2)) + real(substr(sentence, -2, 2))  

list

     +-------------------+
     | sentence   wanted |
     |-------------------|
  1. |    04/06       54 |
  2. |    10/03      123 |
  3. |    02/05       29 |
  4. |    02/00       24 |
     +-------------------+

[John Asherman]

Fair enough

clear
input str5 sentence
"04/06"
"10/03"
"02/05"
"02/00"
end


gen wanted = 12 * real(substr(sentence, 1, 2)) + real(substr(sentence, -2, 2))

list

+-------------------+
| sentence wanted |
|-------------------|
1. | 04/06 54 |
2. | 10/03 123 |
3. | 02/05 29 |
4. | 02/00 24 |
+-------------------+

Works perfectly now, thanks!

[Nick Cox]

In fact that is what Andrew Musau had in #2 so his advice was better all along....