strange output command

[HeatherNewton]

data live_account;
   merge live_account (in=a) purge (in=b) plan (in=c);
   if a and ~b;
   by account_no;
;
   if card_type in ("PL" "TU") then do;
         if plan)num=60000 then outuput;
   end;
   else output;
run;

I would like to translate this to sql but not sure how to do it, could you please help me, thanks.

[sbxkoenk]

Hello,

... cannot be difficult to translate to SQL. 🙂

But your data step cannot work.

Submit it and I think you will get this ERROR:

ERROR 388-185: Expecting an arithmetic operator.

ERROR 200-322: The symbol is not recognized and will be ignored.

The ")" is wrong as well as the spelling of "output" , ... in this statement :

if plan)num=60000 then outuput;

So we do not know what data step should do ... 🙁

Koen

[PaigeMiller]

Ignoring the typos in your code (which really should NOT be there, you should check your code before you post it -- please do that from now on), I believe this WHERE statement in SQL ought to work ... But since you haven't provided data, I cannot test it.

where (card_type in ("PL" "TU") and plan_num=60000) or not card_type in ("PL" "TU") 

[Kurt_Bremser]

You can omit the first part:

where plan_num = 60000 or not card_type in ("PL","TU")

[FreelanceReinh]

The NOT IN condition must be written as variable not in (list) or with additional parentheses: not (variable in (list)).

[PaigeMiller]

Good catch, @FreelanceReinh