As @Kurt_Bremser remarked, your initial data model is not very good. But I know how it is: somebody gave you this Excel file for whatever...   Given this data data tbl; input x1801 x1802 x1803 x1804 x1805 x1806; cards; 1 1 6 1 1 2 2 2 1 2 2 3 3 3 2 3 3 4 4 4 3 4 5 5 5 5 4 8 8 8 . . 5 9 9 10 ; run; I would start by changing it to a long format: data long; set tbl; array months x:; length month $4; if _N_=1 then call symputx('last_month',substr(vname(months(dim(months))),2)); do _N_=1 to dim(months); if months(_N_)=. then continue; month=substr(vname(months(_N_)),2); member_id=months(_N_); output; end; keep month member_id; run; I understand that you do not want to have to enter all the variable names every time, as the number of months  may change. Hence the "x:" construct. To see if a member left the club, we have to see if they were still there the last month. So I used the SYMPUTX to put the last month into a macro variable.   Then, I would sort the data, and finally run through it to see when the individual members came and left: proc sort; by member_id month; run; data want; do until(last.member_id); set long; by member_id; if first.member_id then new=month; end; if month<"&last_month" then left=month; drop month; run; ... View more

What you suggested was this: "I strongly advise to store the raw numerical value (eg 21383 for 2018-07-18) in the macro variable, so you don't have to use "&mydate."d all the time." That is what I thought was a bad idea, as you cannot easily see what a number means. 21384 is not as easy to understand when debugging as "19JUL2018"d. ... View more

You just need to use the date constant format, e.g.: select * from Table where date BETWEEN "&FL_DT"d -&days AND "&FL_DT"d I much prefer that to the solution suggested by @Kurt_Bremser, which would make the code much harder to read.   The alternative is to assign the date constant format to the variable (or another variable, if you prefer that) first: %let FL_DT="&FT_DT"d; select * from Table where date BETWEEN &FL_DT -&days AND &FL_DT ... View more

  Not sure if you can. But it may be possible by using the SQL_functions=ALL option on your libname, hoping that the INTCK() function will be correctly translated: libname oralib Oracle <options> SQL_functions=ALL; Proc sql; create table want as select * from oralib.have where intck('SECOND',timestamp_a,timestamb_b)>2*60*60; quit; There may be a chance that SAS will pass the correct function to Oracle in order to calculate the number of seconds between the two datetimes. But check the results before giving this technique to your students; you may get unpredictable results. ... View more

One possibility is to write your code a temporary file and then execute that: proc contents data= sdtm._all_ noprint out= sdtmall ; run; filename tempsas temp; data _null_; set sdtmall; where upcase(name) ne 'USUBJID'; file tempsas; put 'Proc sort data=sdtm.' memname 'out=_null_ dupout=dups__' memname 'nodupkey;' / ' by usubjid ' name ';' / 'run;' / 'ODS PDF FILE="..pdf";' / 'Title "Dups in sdtm.' memname '";' / 'Proc print data=dups__' memname Width=min; / ' var usubjid ' name ';'/ 'run;'/ 'ODS PDF close;' / ; run; %include tempsas /source2; Before actually submitting the %INCLUDE statement, you may want to have a look at the TEMPSAS file, and perhaps submit the first block of code to see if everything goes well. A lot easier to debug than macros.   Edit note: after submitting the post, I realised that the code (and your original code as well) will generate a lot of statements like proc sort; by usubjid usubjid; run; which is probably not what you want, so I added the WHERE clause to avoid that.   ... View more

Many ways to skin that cat. I would suggest using PRX (Pearl Regular Expressions), as they are worth learning: data want; set a; prxid=prxparse('/^\s*\S+\s+\S+\s+(\S+)/'); drop prxid; length thirdword $10; if prxmatch(prxid,details) then thirdword=prxposn(prxid,1,details); run; A short explanation: ^ Means beginning of string \s* Means zero or more blanks (or other whitespace characters, such as tabs) \S+ Means one or more non-blanks (non-whitespace) \s+ Means one or more blanks (\S+) puts the third occurence of one or more non-blanks in a capture buffer (the one and only in this case).  PRXPOSN then retrieves the contents of the first capture buffer.   But sometimes you need to do stuff where it would be very handy if the variable you wanted to parse was in a file, so that you could use INPUT statements to parse it. In that case you do not have to write the whole dataset to a file and then read it, you can just use the fact that the _INFILE_ variable contains the input buffer, and it can be written to: data want; infile sasautos(verify.sas) ; if _N_= 1 then input @@; set a; _infile_=details; input @1 thirdword $ thirdword $ thirdword $ @@ ; run; I used SASAUTOS(VERIFY.SAS) as the infile, as the file must exist, and this macro seems to exist on most SAS installations. ... View more

Sorry, do you have any problems with the code I submitted? I put the EOF=DONE and the DONE label in to handle the case where there was no delimiter line in the end. If that gives an error, you will have to be more specific, maybe show a sample file and the log. ... View more

There are several ways to skin this cat. One is to create an informat, e.g.: data have; input COUNTRY_CODE $ COUNTRY_SCORE; cards; USA 100 TUR 140 ENG 120 ;run; filename tempsas temp; data _null_; set have end=done; file tempsas; if _N_=1 then put 'invalue score'; put country_code $quote. '=' country_score; if done then put 'other=.;'; run; proc format; %include tempsas /source2; run; data _null_; a='USA'; b=input(a,score.); put _all_; run; Another is to create an index and use that for lookup: proc sql; create index country_code on have(country_code); quit; data _null_; input a $; set have(rename=(country_code=a)) key=a/unique; if _iorc_ then do; _error_=0; country_score=.; end; put _all_; cards; TUR USA FFF ;run; ... View more

It seems to work (although I do not understand your PRX string, but anyway I did not get an error message) if you change the input parameter definition of your package function, e.g.: proc ds2 scond=error; package regexp / overwrite=yes; method match( varchar(50) re, varchar(50) string ) returns integer; return prxmatch(re, string) > 0; end; endpackage; run; quit; ... View more