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.
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
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")
You can omit the first part:
where plan_num = 60000 or not card_type in ("PL","TU")
The NOT IN condition must be written as variable not in (list) or with additional parentheses: not (variable in (list)).
Good catch, @FreelanceReinh
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: