BookmarkSubscribeRSS Feed

Im trying to add age and term together in the final statement but I get this error message -

ERROR: Expression using addition :smileyplus: requires numeric types.

I have check both variables and they seem to be numeric types so im unsure why this error is presenting. Is there any way to get around this please?

 

proc sql;
create table work.borrower_age as
select
AccountNo as accnum,
int(yrdif(DATE_OF_BIRTH,today(),'ACTUAL')) as Age

from
green.optimum201302;

quit;

/*Term Years*/

proc sql;
create table work.term_years as
select
distinct month_date,
accnum,
round(remaining_term_months/12,1) as year

from work.dataset_basel;

quit;

/*Add Age + Year*/

proc sql;
create table work.age_at_term as
select
a.accnum,
b.age,
a.year,
b.age + a.year as age_borr_term

from
work.dataset_baseljun as a
left join work.borrower_age as b
on a.accnum = b.accnum;

quit;


13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Can you pop in some test data, or proc contents for the two tables.  I.e. does accnum have the same type between both tables?

First 4 colums are data dervied from the 2 statements, the 5th column would be the expected output by adding the year and age.

Required year + Age
month_dateAccnumyearAgeAge at Term
May-1412345625860
May-1425645226365
May-1412548926668
May-1425469816667
May-1412558437275
May-1445145435356
May-1415151565359
May-1416584406060
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Maybe easiest to provide a contents of these datasets:

work.term_years

work.borrower_age

work.term_years
month_dateAccnumyear
May-141234562
May-142564522
May-141254892
May-142546981
May-141255843
May-144514543
May-141515156
May-141658440

work.borrower_age
AccnumAge
12345658
25645263
12548966
25469866
12558472
45145453
15151553
16584460
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, a proc contents, i.e. what type the variables are.  What I am trying to acertain is where the type difference comes from and I can't see that directly from the data only the contents.

Is this what you mean? Loacted from the column attributes -

Name Year

Length - Blank

Length - 8

Format = BEST12.

Informat - 12.

Type Numeric

Name Age

Length - Blank

Length - 8

Format = BEST12.

Informat - 12.

Type Numeric

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yep, just run the code:

proc contents data=work.term_years;

run;

/* same for work.borrower_age */

Term Years -  

Data Set Name WORK.TERM_YEARS Observations 58549

Member Type DATA Variables 3

Engine V9 Indexes 0

Created 05 Sep 2014 Fri 12:04:08 o'clock Observation Length 24

Last Modified 05 Sep 2014 Fri 12:04:08 o'clock Deleted Observations 0

Protection Compressed NO

Data Set Type Sorted YES

Label

Data Representation WINDOWS_32

Encoding wlatin1 Western (Windows)

 

Engine/Host Dependent Information

Data Set Page Size 4096

Number of Data Set Pages 349

First Data Page 1

Max Obs per Page 168

Obs in First Data Page 114

Number of Data Set Repairs 0

Filename C:\DOCUME~1\uphl3u\LOCALS~1\Temp\SAS Temporary

Files\_TD6020\term_years.sas7bdat

Release Created 9.0202M3

Host Created XP_PRO

 

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label

2 accnum Num 8 9. 9. Accnum

1 month_date Num 8 MONYY5.

3 year Num 8

 

Sort Information

Sortedby month_date accnum year

Validated YES

Character Set ANSI

Sort Option NODUPKEY

Age borrower

 

The SAS System 09:05 Friday, September 5, 2014 1

The CONTENTS Procedure

Data Set Name WORK.TERM_YEARS Observations 58549

Member Type DATA Variables 3

Engine V9 Indexes 0

Created 05 Sep 2014 Fri 12:04:08 o'clock Observation Length 24

Last Modified 05 Sep 2014 Fri 12:04:08 o'clock Deleted Observations 0

Protection Compressed NO

Data Set Type Sorted YES

Label

Data Representation WINDOWS_32

Encoding wlatin1 Western (Windows)

 

Engine/Host Dependent Information

Data Set Page Size 4096

Number of Data Set Pages 349

First Data Page 1

Max Obs per Page 168

Obs in First Data Page 114

Number of Data Set Repairs 0

Filename C:\DOCUME~1\uphl3u\LOCALS~1\Temp\SAS Temporary

Files\_TD6020\term_years.sas7bdat

Release Created 9.0202M3

Host Created XP_PRO

 

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label

2 accnum Num 8 9. 9. Accnum

1 month_date Num 8 MONYY5.

3 year Num 8

 

Sort Information

Sortedby month_date accnum year

Validated YES

Character Set ANSI

Sort Option NODUPKEY

 

The SAS System 09:05 Friday, September 5, 2014 2

The CONTENTS Procedure

Data Set Name WORK.BORROWER_AGE Observations 63284

Member Type DATA Variables 2

Engine V9 Indexes 0

Created 05 Sep 2014 Fri 12:03:32 o'clock Observation Length 16

Last Modified 05 Sep 2014 Fri 12:03:32 o'clock Deleted Observations 0

Protection Compressed NO

Data Set Type Sorted NO

Label

Data Representation WINDOWS_32

Encoding wlatin1 Western (Windows)

 

Engine/Host Dependent Information

Data Set Page Size 4096

Number of Data Set Pages 252

First Data Page 1

Max Obs per Page 252

Obs in First Data Page 177

Number of Data Set Repairs 0

Filename C:\DOCUME~1\uphl3u\LOCALS~1\Temp\SAS Temporary

Files\_TD6020\borrower_age.sas7bdat

Release Created 9.0202M3

Host Created XP_PRO

 

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat

2 Age Num 8

1 accnum Num 8 BEST12. BEST32.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, that looks fine, however I have just noticed an issue with your sql code:

proc sql;

  create table work.age_at_term as

  select

  a.accnum,

  b.age,

  a.year,

  b.age + a.year as age_borr_term

  from

  work.dataset_baseljun as a    /* Look here, the dataset is given as work.dataset_baseljun - check the attributes of that dataset! */

  left join work.borrower_age as b

  on a.accnum = b.accnum;

quit;

This maybe a typo or you could be looking at the wrong dataset.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Change your SQL to:

proc sql;

  create table work.age_at_term as

  select

  a.accnum,

  b.age,

  a.year,

  b.age + a.year as age_borr_term

  from

  work.term_years as a

  left join work.borrower_age as b

  on a.accnum = b.accnum;

quit;

I have used your SQL and now I get this -

AccnumAgeyearage_borr_term
123456.2.
452154.2.
154447.2.
155548.1.
158588.3.

I had just forgotten to change it back from another test I was working on. So the code still doesnt work even when I revert it back to work.datasetbasel.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, you are confusing me now.  In your first question you gave the two datasets as

  from

  work.term_years as a

  left join work.borrower_age as b

  on a.accnum = b.accnum;

This wasn't working, so I ask you to provide a proc contents on each of these datasets.  The reason is one of these input datasets has a variable which is not numeric.  Now you mention that you are using a completely different dataset - work.datasetbasel.  What is the proc contents of that does it look like:

2 accnum Num 8 9. 9. Accnum

1 month_date Num 8 MONYY5.

3 year Num 8

Each need to be present and numeric - it likely one of them is either missing or character.

I have left on holiday now though, maybe someone else can pickup if still and issue.  My suggestion -> Right Clik on the dataset and show columns on each of the datasets which go into your final sql step, you will see a difference somewhere there.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 13 replies
  • 1638 views
  • 0 likes
  • 2 in conversation