I'm trying to use case statement with looping inside a where clause of proc sql

Question

I'm trying to create Stored process code for multilple prompt values in SAS EG...SO when I run stored process I want to give multiple values for Fac_Nm.....

    PROC SQL;
    CREATE TABLE rpt_fac_prd_schd AS
    select  rpt.fac_id,fac.fac_nm as facility_name, schd.prd_id, prd.prd_nm
                , bid_price_dt , bid_price, Table_top
                , Remaining_resource_count label='rem_res_ct' as rem_res_ct, bid_price_tm
                , today()-Load_Dt as Days_out
    from TD.bid_price_rpt rpt Left Join SSIN.fac fac ON rpt.fac_id=fac.onesrc_fac_id
                     Left Join SSIN.FAC_PRD_SCHD schd ON rpt.fac_id=schd.fac_id 
                        and rpt.bid_price_dt=schd.schd_dt
                    Left Join SSIN.PRD prd ON schd.prd_id=prd.prd_id
/*where fac.Fac_Nm="&Fac_Nm" *//*If I use this it is selecting only first value enetered in the prompt*/
where fac.Fac_Nm =CASE when &Fac_Nm_COUNT = 1 then "&Fac_Nm"
else do i=1 to &Fac_Nm_COUNT;/*Value of &Fac_Nm_COUNT is number of values I selected for Fac_Nm prompt*/
               "&&Fac_Nm&i" %end

            and prd.prd_nm ="&Prd_Nm"
            and calculated Days_Out Between &Days_out_str and &Days_out_end 
            and BID_PRICE_DT = "&arrival_dt"d 
            and BID_PRICE_TM Between "&arrival_str_tm"t and "&arrival_end_tm"t
        Order by fac.fac_nm, prd.prd_nm, bid_price_dt, bid_price , Table_top desc
                 , Remaining_resource_count desc,bid_price_tm desc, Days_out desc;
        QUIT;

But it is throwing following error...Any idea where I'm doing mistake or is there any other way around ???

ERROR:
                        else do i=1 to &Fac_Nm_COUNT;
                                _
                                22
                                76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

score 0 · Answer 1 · answered Mar 28 '13 at 20:20

0

You can't loop inside an SQL statement.

I suggest that you post another question that describers the broader problem you are trying to solve.

answered Mar 28 '13 at 20:20

Aaron Kurtzhals

2,036
3
17
21

score 0 · Answer 2 · answered Mar 28 '13 at 20:21

There is no such thing as "case statement with looping" in SAS. You're combining two things here. You want to do a macro loop here.

where fac.Fac_Nm =
             CASE when &Fac_Nm_COUNT = 1 then "&Fac_Nm"
             else %do i=1 %to &Fac_Nm_COUNT;
                         "&&Fac_Nm&i" %end; end

You also don't want to use CASE WHEN here; that's for the select clause. You want to use macro conditionals, or just do it directly - you don't really need the if at all. IN will happily handle 1 value, and the %do loop will happily handle end-value of 1.

where fac.Fac_NM in (%do i = 1 %to ... %end; )

I'm fairly sure this is the worst way I could possibly imagine to write this query, though; you may want to come back and submit a question with your entire query. You could certainly store all fac_nm's in a single macro variable, with SELECT INTO; but even that's not very smart. The smart way is to do this properly in SQL...

where exists (select 1 from B where A.fac_nm = B.fac_nm);

(assuming your original query comes from A, and your fac_nm's are stored in B). Even better than this is a join between A and B, which is more efficient yet.

I'm trying to use case statement with looping inside a where clause of proc sql

2 Answers2