IFNULL in SAS as far as I know is restricted to FEDSQL use and not for Proc SQL.   The Coalesce functions as @mklangley references will return the left most non-missing value of a list of variables or values (3,4,5 or more) so may be more flexible. Coalescec (var1, var2,var3,'result') would 'result' if all of var1 through 3 were missing (or null for those who haven't quite mastered the SAS jargon yet). ... View more

Running this:  %if %sysfunc( WEEKDAY( %sysfunc(today())) )) as an expression worked.  ... View more

Hi @ChrisNZ,   What a great question. It is best to have questions in their own topic, So, create a new post and include my name (@JBailey) because the site will ping me. I can answer the question, quickly.   Best wishes, Jeff ... View more

Depending on what you need to scale to, may be better to go to MDY() function using period as month and day as 15 and year from academic period. ... View more

Yes. You are missing the fact that Oracle does not actual have a DATE data type.  It only has DATETIME.  Any reasonable number of days will look like some time early in the morning of January first 1960.   I would work better to just use SAS code to add data, but if you want to use the SQL INSERT statement then give it a datetime value. values('05MAY2023:00:00'DT)   ... View more

@ErikLund_Jensen  A good reminder to avoid datalines/card within production worthy DIS jobs unless there is a very good reason for it. For me the only place where I might consider this is in adhoc jobs for maintenance of control tables - and these jobs are then for a BAU team only, don't get scheduled but executed directly out of DIS if any change is required. ...and I'm only using this approach on sites where I don't trust a BAU team that they will be "able" to manually maintain a config text file which a BAU adhoc job could load into a control table. ... View more

If you find that you frequently need to macro quote the arguments to your macro you might find it easier to pass the values with actual quotes. Especially if you are passing the values with SAS code where you could use the QUOTE() function to add the quotes. Just make your macro smart enough to remove the quotes. Assuming you actually need to remove them, since most values that would require elaborate quoting to be used a macro call are probably going to be used by the macro to generate SAS code where you would just have to add the quotes back anyway.   Here is an example where the macro can pull off the quotes (if there) and then add them back when needed.   %macro mymacro(parm1,parm2); %local q1 q2 ; %let parm1=%qsysfunc(dequote(&parm1)); %let parm2=%qsysfunc(dequote(&parm2)); %let q1=%sysfunc(quote(&parm1,%str(%'))); %let q2=%sysfunc(quote(&parm2,%str(%'))); %put &=parm1 &=parm2; %put &=q1 &=q2; data _null_; put 'parm1=' &q1 ; put 'parm2=' &q2 ; put "Both = &parm1 &parm2" ; run; %mend mymacro; Example calls: 73 options mprint; 74 %mymacro(parm1=Hi there,parm2='Has a,comma'); PARM1=Hi there PARM2=Has a,comma Q1='Hi there' Q2='Has a,comma' MPRINT(MYMACRO): data _null_; MPRINT(MYMACRO): put 'parm1=' 'Hi there' ; MPRINT(MYMACRO): put 'parm2=' 'Has a,comma' ; MPRINT(MYMACRO): put "Both = Hi there Has a,comma" ; MPRINT(MYMACRO): run; parm1=Hi there parm2=Has a,comma Both = Hi there Has a,comma NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 75 %mymacro(parm1='Hi there',parm2=%bquote(Has a,comma)); PARM1=Hi there PARM2=Has a,comma Q1='Hi there' Q2='Has a,comma' MPRINT(MYMACRO): data _null_; MPRINT(MYMACRO): put 'parm1=' 'Hi there' ; MPRINT(MYMACRO): put 'parm2=' 'Has a,comma' ; MPRINT(MYMACRO): put "Both = Hi there Has a,comma" ; MPRINT(MYMACRO): run; parm1=Hi there parm2=Has a,comma Both = Hi there Has a,comma NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 76 %mymacro(parm1="Hi there",parm2=%bquote(Hasn't a comma)); PARM1=Hi there PARM2=Hasn't a comma Q1='Hi there' Q2='Hasn''t a comma' MPRINT(MYMACRO): data _null_; MPRINT(MYMACRO): put 'parm1=' 'Hi there' ; MPRINT(MYMACRO): put 'parm2=' 'Hasn''t a comma' ; MPRINT(MYMACRO): put "Both = Hi there Hasn't a comma" ; MPRINT(MYMACRO): run; parm1=Hi there parm2=Hasn't a comma Both = Hi there Hasn't a comma NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ... View more

@DavidPhillips2  - I suspect that using PROC CONTENTS against a SAS library using the META engine will only work if the library has tables that are actually registered in SAS metadata - if it will work at all. There is also no guarantee that the registered table definition will match the actual table definition. So I guess it depends what you want to do here. Review the tables as stored on disk or how they are defined in SAS metadata.     ... View more

Slightly shorter code.  I didn't realize I could just call the column name directly in this case.  the -1 reference in the code was not needed.   %global curProgram; proc sql; create table AllProgramList ( SMRPRLE_PROGRAM varchar(100) ); insert into AllProgramList(SMRPRLE_PROGRAM) values ("One"); insert into AllProgramList(SMRPRLE_PROGRAM) values ("Two"); ; data _null_; set AllProgramList; file code; Call symputx('curprogram', SMRPRLE_PROGRAM); run; %put curProgram &curProgram; ... View more