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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: