topic Re: Sub-sql with Case in SAS Programming

Sub-sql with Case

zz — Wed, 19 Oct 2016 21:34:47 GMT

I am working on the below sql and sub-sql statement:

PROC SQL;

CREATE TABLE JR.PEAK AS
SELECT s.USUBJID FROM JR.SV_SRC s WHERE s.SVSTDTC NE "" AND s.SVPENRFN=2
;

CREATE TABLE JR.SV_SRC_STEP1 AS
SELECT SV.*
, ( SELECT ( CASE WHEN SV.USUBJID IN (SELECT p.USUBJID FROM JR.PEAK p) THEN "Y" ELSE "N" END ) ) AS P_2
FROM JR.SV SV
;
QUIT;

It gives me the below error msg:

270 CREATE TABLE JR.SV_SRC_STEP1 AS
271 SELECT SV.*
272 , ( SELECT ( CASE WHEN SV.USUBJID IN (SELECT p.USUBJID FROM JR.PEAK p) THEN "Y" ELSE "N" END ) ) AS PERIOD2
_
22
76
272 ! LABEL="Assigned to Period 2 (Y/N)"
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,
AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,
LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

Any help or suggestions will be appreciated!

Re: Sub-sql with Case

Reeza — Thu, 20 Oct 2016 00:05:56 GMT

Wouldn't a join be easier?

Re: Sub-sql with Case

PGStats — Thu, 20 Oct 2016 01:38:43 GMT

I think your query could be simplified to :

PROC SQL;

CREATE TABLE JR.PEAK AS
SELECT USUBJID 
FROM JR.SV_SRC 
WHERE SVSTDTC NE "" AND SVPENRFN=2;

CREATE TABLE JR.SV_SRC_STEP1 AS
SELECT 
	*,
	CASE 
		WHEN USUBJID IN (SELECT USUBJID FROM JR.PEAK) THEN "Y" 
		ELSE "N" END AS P_2
FROM JR.SV;

QUIT;

(untested)

Re: Sub-sql with Case

RW9 — Thu, 20 Oct 2016 08:24:37 GMT

Or very simply:

proc sql;
  create table JR.SV_SRC_STEP1 as
  select *,
         case when USUBJID in (select USUBJID from JR.SV_SRC where SVSTDTC ne "" and SVPENRFN=2) then "Y" 
  	      else "N" end AS P_2
  from JR.SV;
quit;