CODEWITHSUNDEEP
sas
viji
viji

Reputation: 477

PROC SQL throwing error in cast statement at 'as' and alias at 'as'

PROC SQL;
SELECT end_dt-start_dt as EXPOSURE,
 (CASE WHEN (EXPOSURE)<0 THEN 0 ELSE CAST(TRUNC((EXPOSURE)/30+0.99) as 
INTEGER)  END as bucket) FROM TABLE

This statement works fine in SQL but throws an error in proc sql at both 'as'.

Upvotes: 1

Views: 4815

Answers (2)

VigneshSubramanian
VigneshSubramanian

Reputation: 1

SAS has attributes for every field like Length, Format, Informat. They help store, read and read from a data source.

Your PROC SQL would not require a type cast. Instead use FORMAT statement. 

PROC SQL; SELECT end_dt-start_dt as EXPOSURE, CASE WHEN (EXPOSURE)<0 THEN 0 ELSE INT(TRUNC((EXPOSURE)/30+0.99)) END as bucket Format 8. FROM TABLE

Not sure or syntax of the whole statement as I couldn't get to test it, although the whole idea holds true.

Upvotes: 0

Reeza
Reeza

Reputation: 21264

CAST is not a valid SAS SQL function. Use the appropriate SAS SQL function, in this case likely INT(), to convert calculation to an integer value.

If you'd like to use your DB SQL you need to use SAS SQL Pass Through which will pass the code directly to your database, but then the entire query must be valid on that database.

Upvotes: 3

Related Questions