topic Re: Add 2 variables in SAS Programming

Add 2 variables

anonymous_user — Fri, 05 Sep 2014 13:04:03 GMT

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;

Re: Add 2 variables

RW9 — Fri, 05 Sep 2014 13:12:34 GMT

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?

Re: Add 2 variables

anonymous_user — Fri, 05 Sep 2014 13:27:53 GMT

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

Re: Add 2 variables

RW9 — Fri, 05 Sep 2014 13:33:21 GMT

Maybe easiest to provide a contents of these datasets:

work.term_years

work.borrower_age

Re: Add 2 variables

anonymous_user — Fri, 05 Sep 2014 13:38:45 GMT

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

Re: Add 2 variables

RW9 — Fri, 05 Sep 2014 13:44:26 GMT

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.

Re: Add 2 variables

anonymous_user — Fri, 05 Sep 2014 13:49:16 GMT

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

Re: Add 2 variables

RW9 — Fri, 05 Sep 2014 14:04:27 GMT

Yep, just run the code:

proc contents data=work.term_years;

run;

/* same for work.borrower_age */

Re: Add 2 variables

anonymous_user — Fri, 05 Sep 2014 14:12:54 GMT

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.

Re: Add 2 variables

RW9 — Fri, 05 Sep 2014 14:19:52 GMT

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.

Re: Add 2 variables

RW9 — Fri, 05 Sep 2014 14:21:53 GMT

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;

Re: Add 2 variables

anonymous_user — Fri, 05 Sep 2014 14:27:12 GMT

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.

Re: Add 2 variables

anonymous_user — Fri, 05 Sep 2014 14:38:01 GMT

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	.

Re: Add 2 variables

RW9 — Fri, 05 Sep 2014 15:56:10 GMT

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.