@burtsm  An alternative approach is to use a hash table lookup. You first load the hash table with the id's matching the condition you don't want. You then delete all records who have a match with the hash table over id. data have; input id firstbirth birthnum; datalines; 1 1 1 1 1 2 2 1 1 2 0 2 2 0 3 3 1 1 3 1 2 3 1 3 4 1 1 4 0 2 4 0 3 ; data want; if _n_=1 then do; dcl hash h1(dataset:'have(where=(FIRSTBIRTH=1 and BIRTHNUM ne 1))'); h1.defineKey('id'); h1.defineDone(); end; set have; if h1.check()=0 then delete; run; proc print data=want; run; Using hash tables has the big advantage that your source data doesn't need to be sorted. ... View more

The basics:  the SCAN function extracts words from a string.  The second parameter (1, 2, 3) indicates which word to extract.  You can find all the details about SCAN in the online documentation.   Two vertical bars join character strings.  There are many more modern functions that can accomplish all of that and more (CAT, CATT, CATS, CATX).   ... View more

Just add them to your WHERE clause. And yes, you can create a look up data set containing those values (one per row) and then use the NOT IN (SELECT name FROM Lookupdataset) construct in the WHERE clause as well. ... View more

@SoozMorg I would be using the SQL code you've initially posted to ensure that your SQL executes in-database. Using SAS functions which can't get converted into database functions will cause the SQL to pull the data into SAS for execution.   For dealing with the resulting datetime strings in SAS macro code I'd be going for the approach @Reeza already posted.   If fractional seconds are of importance for what you're doing then I'd be using INPUTN() instead of "..."dt syntax within your macro code. Have a look at the result from below code sample which illustrates the differences. %macro test(max_ts,new_maxts); %put using ""dt syntax; %if %sysevalf("&new_maxts"dt >"&max_ts"dt) %then %put RESULT: GT; %else %if %sysevalf("&new_maxts"dt = "&max_ts"dt) %then %put RESULT: EQ; %else %put RESULT: LT; %put using inputn() syntax; %if %sysevalf(%sysfunc(inputn("&new_maxts",datetime26.)) > %sysfunc(inputn("&max_ts",datetime26.))) %then %put RESULT: GT; %else %if %sysevalf(%sysfunc(inputn("&new_maxts",datetime26.)) = %sysfunc(inputn("&max_ts",datetime26.))) %then %put RESULT: EQ; %else %put RESULT: LT; %mend; %test(29JUN2017:23:59:52.000000,01JUL2017:23:59:52.000000); %test(29JUN2017:23:59:52.000000,29JUN2017:23:59:52.000000); %test(29JUN2017:23:59:52.000001,29JUN2017:23:59:52.000000); %test(29JUN2017:23:59:52.000010,29JUN2017:23:59:52.000000); %test(29JUN2017:23:59:52.000100,29JUN2017:23:59:52.000000); %test(29JUN2017:23:59:52.001000,29JUN2017:23:59:52.000000); 42 %test(29JUN2017:23:59:52.000000,29JUN2017:23:59:52.000000); using ""dt syntax RESULT: EQ using inputn() syntax RESULT: EQ 43 44 %test(29JUN2017:23:59:52.000001,29JUN2017:23:59:52.000000); using ""dt syntax RESULT: EQ using inputn() syntax RESULT: EQ 45 %test(29JUN2017:23:59:52.000010,29JUN2017:23:59:52.000000); using ""dt syntax RESULT: EQ using inputn() syntax RESULT: LT 46 %test(29JUN2017:23:59:52.000100,29JUN2017:23:59:52.000000); using ""dt syntax RESULT: EQ using inputn() syntax RESULT: LT 47 %test(29JUN2017:23:59:52.001000,29JUN2017:23:59:52.000000); using ""dt syntax RESULT: EQ using inputn() syntax RESULT: LT   ... View more

@slchen It's a good habit to declare your variables as local. You'll save yourself many headaches. So this is a (slightly) better macro: %macro test/minoperator mindelimiter='|'; %local list part; %let list=13|14|15|25|26|27; %do part = 1 %to 30; %if &part in &list %then %put &=part; %end; %mend; %test; ... View more

Hi: This post is remarkably similar (uses the same number as this posting https://communities.sas.com/t5/Base-SAS-Programming/Importing-sas7bdat-file/m-p/370282#M88434) 201403 as an example of a number that is a date variable. Homework?? Another possible answer was posted in the other forum. Also posted with a longer explanation of how date variables work. cynthia ... View more