Learning SAS? Welcome to the exclusive online community for all SAS learners.

How to create a subquery in case statement

Reply
Occasional Contributor
Posts: 17

How to create a subquery in case statement

Hi Experts,

I want to create a subquery in case statement like following:

proc sql;

create table airsales as

select,

case

when market = "Nzl" then

(select *

from air_sales as ais

left join &market._branch as br

on ais.branch = br.branch

left join &market._app as ap

on ais.tourcode = ap.tour_code_trim

and ais.valcarr = ap.airline)

when market = "Aus" then

(select *

from air_sales as ais

left join &market._app as ap

on ais.tourcode = ap.tour_code_trim

and ais.valcarr = ap.airline)

end as market; // GETTING SYNTAX ERROR HERE

Please advice as to how should I create subquery.

Super User
Super User
Posts: 6,497

Re: How to create a subquery in case statement

First you have comma after the SELECT statement.

Second it makes no sense to have a SELECT * inside a CASE statement.  A CASE statement is used to conditional generate the value of ONE variable.

Third you have no FROM clause for your main query.

Fourth get the query working without any macro variable references before introducing the complexity of using code generation by expanding macro varaibles.

Why not just join the tables in the FROM clause?

Super User
Super User
Posts: 6,497

Re: How to create a subquery in case statement

Perhaps what you really want is just a UNION of two queries.

create table airsales as

select "Nzl" as market, *

from ......

UNION

select "Aus" as market, *

from ......


;

Occasional Contributor
Posts: 17

Re: How to create a subquery in case statement

Hi Tom,

I am trying to create a code wherein if my market is NZL, I want the joins from 2 tables but if my market is AUS then I want join from one table. The market is getting passed in a macro variable. So I am not sure how should I use subquery in this case

Respected Advisor
Posts: 4,641

Re: How to create a subquery in case statement

There are many problems with this query. It is not clear to me what you are tying to do.

  • There should be no comma after SELECT.
  • Your query does not have a FROM clause. From what table is market in "when market = "Nzl"" supposed to come from?
  • The CASE expression syntax is CASE WHEN condition THEN value ... . If value is a subquery then it should return a single value.

PG

PG
Ask a Question
Discussion stats
  • 4 replies
  • 722 views
  • 0 likes
  • 3 in conversation