topic Format issues with Proc Sql in SAS Programming

Format issues with Proc Sql

saspert — Thu, 20 Feb 2014 22:03:37 GMT

Hi,

I am not able to troubleshoot the errors with the proc sql. I have attached the log with the error in the text file since I am not able to copy paste the code here. The question is - there is no comparison operator (eq/gt/ge) being used in this proc sql but the error says the data types being compared are different.

Here in the code the proc sql dataset _7_casecntofacct sources from qm0casecountofaccount1 (a sas proc sql view ) which in turn uses another proc sql sas view qmbcaseuseacctbycycle which has the comparison operators (=).

The log does not give any errors but only says -

Note: sql view xxx has been defined.

So only when the proc sql create table is executed, does the error with the different data types show up? I hope I am making sense.

Thanks,

saspert.

Re: Format issues with Proc Sql

Reeza — Thu, 20 Feb 2014 22:47:47 GMT

Have you verified each view individually?

Re: Format issues with Proc Sql

DBailey — Thu, 20 Feb 2014 23:25:26 GMT

SAS Views are not evaluated other than for syntax at create time. I would suspect that if you just performed a select * from the view, you would get the same error.

Re: Format issues with Proc Sql

saspert — Tue, 25 Feb 2014 19:52:41 GMT

Thanks Reeza and DBailey for the suggestions. I was able to troubleshoot. I guess sas views are compiled at run time. They are executed only when a sas dataset (or in other words a materialized view) using those views is being created. Is that correct understanding?

The error was in a join between two different datasets using the same field but different format.

Re: Format issues with Proc Sql

DBailey — Tue, 25 Feb 2014 21:11:41 GMT

correct.