proc SQL equivalent of put(sum(a.cohort,&yrs_to_track.),6.) in inner join

[DavidPhillips2]

Please advise on the proc sql version of the below:

cohort is a number like 200110, 200210 for YYYYCustom that represents a year plus an academic semester.

1000 is a way of adding 10 years.

inner join odsstu.vcu_degrees_awarded B
B.academic_period_graduation <= put(sum(a.cohort,1000),6.)

[smantha]

I guess it works for years. Why change it? How does one increment semester that is when it might be a problem.

[DavidPhillips2]

It works only in a datastep but not proc sql

[DavidPhillips2]

ERROR: Function SUM requires a numeric expression as argument 1.
ERROR: Numeric format F in PUT function requires a numeric argument.

[smantha]

So Datastep does a conversion and sql does not? Is this a passthru, does data conversion like input or putn notwork?

[DavidPhillips2]

sum is a reserved word in SQL so I can't use it inside of a put statement.  I need the conversion.

[DavidPhillips2]

In this case, I'm writing to the work library.

[Reeza]

I usually multiply it by 100 to add the extra zeros.

Can you post the exact log? I'd expect that to work, unless it's in a SQL pass through. 

(a.cohort*100 + &yrs_to_track)

---

@DavidPhillips2 wrote:

sum is a reserved word in SQL so I can't use it inside of a put statement.  I need the conversion.

---

[Kurt_Bremser]

---

@DavidPhillips2 wrote:

ERROR: Function SUM requires a numeric expression as argument 1.
ERROR: Numeric format F in PUT function requires a numeric argument.

---

So you only got away with your sloppy programming because the data step does automatic conversions. In SQL, you have to do the conversion explicitly. The NOTEs about automatic conversions in the data step log should have alerted you to this.

Maxim 2: Read the Log

Maxim 25: Have a Clean Log

Maxim 31: Computers are Dumb. Don't rely on their automatics.

Start by applying the INPUT() function as @PGStats suggested.

And then start to clean out such issues from all your codes, whenever you find them.

[mklangley]

Could you please post your entire query? 

[PGStats]

inner join odsstu.vcu_degrees_awarded B
B.academic_period_graduation <= put(sum(input(a.cohort, best.), 1000), 6.)