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;
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_date | Accnum | year | Age | Age at Term |
May-14 | 123456 | 2 | 58 | 60 |
May-14 | 256452 | 2 | 63 | 65 |
May-14 | 125489 | 2 | 66 | 68 |
May-14 | 254698 | 1 | 66 | 67 |
May-14 | 125584 | 3 | 72 | 75 |
May-14 | 451454 | 3 | 53 | 56 |
May-14 | 151515 | 6 | 53 | 59 |
May-14 | 165844 | 0 | 60 | 60 |
Maybe easiest to provide a contents of these datasets:
work.term_years
work.borrower_age
work.term_years | ||
month_date | Accnum | year |
May-14 | 123456 | 2 |
May-14 | 256452 | 2 |
May-14 | 125489 | 2 |
May-14 | 254698 | 1 |
May-14 | 125584 | 3 |
May-14 | 451454 | 3 |
May-14 | 151515 | 6 |
May-14 | 165844 | 0 |
work.borrower_age | |
Accnum | Age |
123456 | 58 |
256452 | 63 |
125489 | 66 |
254698 | 66 |
125584 | 72 |
451454 | 53 |
151515 | 53 |
165844 | 60 |
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
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.
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.
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 -
Accnum | Age | year | age_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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.