topic Re: proc SQL equivalent of put(sum(a.cohort,&yrs_to_track.),6.) in inner join in SAS Programming

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

DavidPhillips2 — Wed, 10 Jun 2020 18:52:29 GMT

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

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

smantha — Wed, 10 Jun 2020 19:06:20 GMT

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

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

DavidPhillips2 — Wed, 10 Jun 2020 19:07:59 GMT

It works only in a datastep but not proc sql

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

DavidPhillips2 — Wed, 10 Jun 2020 19:08:51 GMT

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

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

smantha — Wed, 10 Jun 2020 19:13:24 GMT

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

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

DavidPhillips2 — Wed, 10 Jun 2020 19:14:34 GMT

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

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

DavidPhillips2 — Wed, 10 Jun 2020 19:15:34 GMT

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

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

mklangley — Wed, 10 Jun 2020 19:19:00 GMT

Could you please post your entire query?

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

Reeza — Wed, 10 Jun 2020 19:19:08 GMT

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.

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

PGStats — Wed, 10 Jun 2020 19:19:17 GMT

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

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

Kurt_Bremser — Thu, 11 Jun 2020 05:13:58 GMT

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