SELECT left(ageatdeath,(len(ageatdeath)-5)) as [AGE_VAL], left(ageatdeath,2) AS [AGE_Units] FROM #DEATH

SELECT left(ageatdeath,(len(ageatdeath)-5) as [AGE_VAL], left(ageatdeath,2) AS [AGE_Units] FROM DEATH

(len(ageatdeath)-5)   -- yours
(len(ageatdeath)-5))  -- correct code

CREATE TABLE #Persons
    (Name VARCHAR(20), Birth_Date DATE, Death_Date DATE
    -- Use a computed column if you need the text string (n)nnyears
    ,AgeAtDeath AS (CAST(DATEDIFF(year, Birth_Date, Death_Date) AS VARCHAR(3)) + 'years'))

INSERT INTO #Persons
SELECT 'Grumpy', DATEADD(year, -51, '2012-05-10') - ABS(CHECKSUM(NEWID())) % 100, '2012-05-10'
UNION ALL SELECT 'Sleepy', DATEADD(year, -62, '2012-04-23') - ABS(CHECKSUM(NEWID())) % 100, '2012-04-23'
UNION ALL SELECT 'Dopey', DATEADD(year, -45, '2012-03-15') - ABS(CHECKSUM(NEWID())) % 100, '2012-03-15'

SELECT Name, Birth_Date, Death_Date, AgeAtDeath
    -- Parse the text string like this
    ,AGE=LEFT(AgeAtDeath, PATINDEX('%[^0-9]%', AgeAtDeath)-1)
    -- Or just do it the right way
    ,AGE=DATEDIFF(year, Birth_Date, Death_Date)
FROM #Persons

DROP TABLE #Persons