topic Re: Converting SQL code into SAS code in New SAS User

Converting SQL code into SAS code

wheddingsjr — Mon, 04 Jan 2021 20:36:59 GMT

Good afternoon all

I am trying to convert SQL code into SAS code and keep getting multiple errors, I'm guessing because some of the expressions I am using do not translate. Can anyone give me any suggestions?

Here is the code I am using:

PROC SQL;
CREATE TABLE work.first_chemo AS 
SELECT 
CASE WHEN Month(DischargeDTS) > 9 
	 THEN YEAR(DischargeDTS) + 1 
	 ELSE YEAR(DischargeDTS)
END as Fiscal_Year
, b.MRN AS mrn
, MIN(DischargeDTS) AS date
	,CASE WHEN LEN(c.CPT) ^= 5 
		THEN c.HCPCS 
		ELSE c.CPT 
END AS HCPCS
FROM epicfin.HospitalAccount a 
	LEFT JOIN epicpat.Patient b
	 	ON a.PatientID= b.PatientID
	LEFT JOIN Epicfin.HospitalTransaction c
		ON a.HospitalAccountID = c.HospitalAccountID
;QUIT;

and this is the log with the errors:

26         PROC SQL;
27         CREATE TABLE work.first_chemo AS
28         SELECT
29         CASE WHEN Month(DischargeDTS) > 9
30         	 THEN YEAR(DischargeDTS) + 1
31         	 ELSE YEAR(DischargeDTS)
32         END as Fiscal_Year
33         , b.MRN AS mrn
34         , MIN(DischargeDTS) AS date
35         	,CASE WHEN LEN(c.CPT) ^= 5
36         		THEN c.HCPCS
37         		ELSE c.CPT
38         END AS HCPCS
39         FROM epicfin.HospitalAccount a
40         	LEFT JOIN epicpat.Patient b
41         	 	ON a.PatientID= b.PatientID
42         	LEFT JOIN Epicfin.HospitalTransaction c
43         		ON a.HospitalAccountID = c.HospitalAccountID
44         ;
ERROR: Function MONTH requires a numeric expression as argument 1.
ERROR: Expression using greater than (>) has components that are of different data types.
ERROR: Function YEAR requires a numeric expression as argument 1.
ERROR: Expression using addition (+) requires numeric types.
ERROR: Function YEAR requires a numeric expression as argument 1.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Function LEN could not be located.
ERROR: Expression using not equals (^=) has components that are of different data types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
44       !  QUIT;
NOTE: The SAS System stopped processing this step because of errors.

Any assistance provided would be greatly appreciated.

Thanks

Re: Converting SQL code into SAS code

PaigeMiller — Mon, 04 Jan 2021 20:45:26 GMT

MONTH and YEAR functions only works on numeric variables. The error message is telling you that DischargeDTS is not numeric. So you would have to convert it to numeric. If you can show us a typical value of DischargeDTS, we can tell you how to convert it. Furthermore, DischargeDTS needs to be an actual SAS date, which is the number of days since 01JAN1960.

Re: Converting SQL code into SAS code

ballardw — Mon, 04 Jan 2021 21:05:48 GMT

Also If "LEN(c.cpt)" is supposed to return the number of characters then the function name is LENGTH in SAS. Otherwise you will need to explain what "LEN" is supposed to do.

Re: Converting SQL code into SAS code

wheddingsjr — Mon, 04 Jan 2021 21:58:13 GMT

Thanks Ballard, yes the function is Length.

Re: Converting SQL code into SAS code

wheddingsjr — Mon, 04 Jan 2021 22:00:56 GMT

Thanks Paige

In the SAS table the DischargeDTS vaue looks like this:
2015-06-09 00:00:00.0000000

when copied and pasted to excel it looks like:
6/9/2015 12:00:00 AM

Re: Converting SQL code into SAS code

Tom — Mon, 04 Jan 2021 22:26:08 GMT

Run PROC CONTENTS on your source datasets so you can see how the variables are defined.

If the values of DischargeDTS look like "2015-06-09 00:00:00.0000000" then they are probably character strings as most SAS datetime formats will not display in that way. So you will want to first convert them to actual dates before trying to use the MONTH() or YEAR() functions.

Month(input(DischargeDTS,yymmdd10.)) > 9

Also you need to figure out which of CPT and HCPCS is numeric and which is character. Either way you probably will want to create your new HCPCS variable as a character string. So if CPT is character already your code might need to be

,CASE WHEN LENGTH(c.CPT) ^= 5 
		THEN cats(c.HCPCS)
		ELSE c.CPT 
END AS HCPCS

Re: Converting SQL code into SAS code

wheddingsjr — Tue, 05 Jan 2021 16:58:09 GMT

Thanks Tom

That worked perfectlt!!!!