DATA Step, Macro, Functions and more

Sub-sql with Case

Accepted Solution Solved
Reply
Contributor zz
Contributor
Posts: 28
Accepted Solution

Sub-sql with Case

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!


Accepted Solutions
Solution
‎10-19-2016 10:34 PM
Respected Advisor
Posts: 4,649

Re: Sub-sql with Case

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)

PG

View solution in original post


All Replies
Super User
Posts: 17,836

Re: Sub-sql with Case

Wouldn't a join be easier? 

Solution
‎10-19-2016 10:34 PM
Respected Advisor
Posts: 4,649

Re: Sub-sql with Case

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)

PG
Super User
Super User
Posts: 7,401

Re: Sub-sql with Case

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 312 views
  • 0 likes
  • 4 in conversation