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.
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.
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.