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!
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)
Wouldn't a join be easier?
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)
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.