Add 2 variables

Reply
anonymous_user
Posts: 0

Add 2 variables

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;


Super User
Super User
Posts: 7,720

Re: Add 2 variables

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?

anonymous_user
Posts: 0

Re: Add 2 variables

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
Super User
Super User
Posts: 7,720

Re: Add 2 variables

Maybe easiest to provide a contents of these datasets:

work.term_years

work.borrower_age

anonymous_user
Posts: 0

Re: Add 2 variables

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
Super User
Super User
Posts: 7,720

Re: Add 2 variables

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.

anonymous_user
Posts: 0

Re: Add 2 variables

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

Super User
Super User
Posts: 7,720

Re: Add 2 variables

Yep, just run the code:

proc contents data=work.term_years;

run;

/* same for work.borrower_age */

anonymous_user
Posts: 0

Re: Add 2 variables

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.

Super User
Super User
Posts: 7,720

Re: Add 2 variables

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.

Super User
Super User
Posts: 7,720

Re: Add 2 variables

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;

anonymous_user
Posts: 0

Re: Add 2 variables

I have used your SQL and now I get this -

AccnumAgeyearage_borr_term
123456.2.
452154.2.
154447.2.
155548.1.
158588.3.
anonymous_user
Posts: 0

Re: Add 2 variables

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.

Super User
Super User
Posts: 7,720

Re: Add 2 variables

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.

Ask a Question
Discussion stats
  • 13 replies
  • 426 views
  • 0 likes
  • 2 in conversation