Untested but should work: data new;    set old;    if tot ge 10;    if rep = 'N'  then call missing(of var:);  /* or call missing(of var1-var7);  read the doc on "of" keyword and variable lists */ run; ... View more

Hi Art, 1) The SAS Work and SAS Util directories are created upon SAS invocation.  I don't believe &sysuserid would be available at that time. 2) If this understanding is incorrect, how would you set this in the system wide configuration file?  I know I can set the -work and -util root directories, but I believe _TD_<processid>_<machinename>_ is baked into SAS.  I was hoping there is some unknown to me option that would allow me to change this naming convention. I guess there could be some repercussions I need to think about.  For example, it could break the "cleanwork" utility. Regards, Scott ... View more

All our work and SAS Util directories are on a specific drive on our server reserved just for that purpose.  This prevents a runaway job from consuming space on a single communal drive. SAS creates the work sub-directories under this root directory.  Since SAS obviously knows the process ID, and since the process ID has an owner, I was hoping to coax SAS into adding that owner to the generated physical directory name. ... View more

P.S.:  I've just read PGStats' solution more closely, and mine isn't that much different than PGStats' solution.  Use whichever one works for you.  But do wrap your head around "greedy" vs. "non-greedy" - it applies to both solutions. ... View more

ZRick wrote: I just want: "IR_MX_GOVT_BFV_MXN" , or trim off the piece to the right of the last underscore, including the underscore. How do I do that? data t; crv='IR_MX_GOVT_BFV_MXN_7Y'; run; Another regular expression solution: data t; crv='IR_MX_GOVT_BFV_MXN_7Y'; * crv='IRMXGOVTBFVMXN7Y'; new1a=prxchange("s/^(.*)_(.*)$/\1/o",-1,crv); new1b=prxchange("s/^(.*)_(.*)$/\2/o",-1,crv); new2a=prxchange("s/^(.*?)_(.*)$/\1/o",-1,crv); new2b=prxchange("s/^(.*?)_(.*)$/\2/o",-1,crv); run; I'll explain the regular expression: s = "substitute" / / / = delimiter for "from", "to" ^ = beginning of text ( ) = capture buffer, numbered in order.  So, there are two capture buffers. . = any character * = zero or more occurrences. $ = end of text.  Note: the padding of SAS variables with spaces are significant.  With a different regular expression, you might need "trim(crv)" \1 = the text captured in the first capture buffer o = compile the regular expression once, and retain the regex across data step iterations.  Irrelevant in this example, but often used to increase performance. -1 = apply the substitution to all occurrences of the regular expression in the source text (doesn't apply to this regex, since it encompasses the entire string). Ok, so in words, the first regular expression says "from the beginning of the text, find all characters ***, an underscore, then all characters until the end of the text, and substitute the text captured in the first capture buffer". *** A key concept here is "greedy" vs. "non-greedy" matching (Google "regular expressions greedy matching").  By default, the matching is "greedy".  So, (.*) matches as many occurrences as it can, while still satisfying the rest of the regular expression _(.*)$  (underscore, any characters, end of line). In the second regular expression, the ? metacharacter means "non-greedy" matching.  So, (.*?) means match the minimum occurrences, while still satisfying the rest of the regular expression (which is a "greedy" match). I've saved the results of capture buffer #2 so you can see the results of the greedy vs. non-greedy matching. Uncomment the second crv setting so show that crv will be returned unchanged if it doesn't match the regular expression. Hope this helps, Scott ... View more

All great comments (pun intended!) to the OP. Adding some thoughts: * As Tom says, document the business logic, rather than obvious technical logic.  IOW, say why are you sorting by a b c, not that that next block of code is (obviously) sorting by a b c. * Ditch the trailing */ on a multi-line block comment.  It adds no value, and is either a pain to keep aligned, or a waste of programmer time if you try to do so.  We're programmers, not ASCII artists.  IOW, /**************************** * Description: * More Stuff * ***************************/ Not /****************************/ * Description:                      */ * More Stuff                        */ * ***************************/ Same for trailing semi-colons on * ; style comments. * I tend to put no space between code and trailing semi-colon, and a space between comment and trailing semi-colon, but that is very much a personal coding style.  I've seen others that put a space before all semi-colons. * Sort by keys and todate to retain the most recent record ; proc sort data=foo;   by key1 key2 todate; run; * Retain the last record ; data bar;   set foo;   by key1 key2 todate;   if last.todate; run; * Within macro code, * ; style comments will print in the log, /* */ and %* ; style comments will not.  So, whether you are trying to document the code, or the generated code determine which commenting style to use. * A "trick" I occasionally use, esp. during development, and more esp. if I've inherited code over using /* */ block comments, is to use an uncalled macro to comment a large block of code: /* The previous programmer */ /* used block style comments */ /* everywhere, outside of the */ /* program header */ proc foo; run; %macro comment; /* More block comments here */ data whatever; run; /* some final comments */ /* why didn't they just use statement style comments? */ proc bar; run; %mend; I'm not advocating this as a best practice by any means, just saying it's another "commenting code out" approach that's sometimes useful during development. * Ask 20 programmers (try to pick good ones lol), you'll get 20 program header block styles.  Review a few and decide what works for you.  Or your company may come up with a standard program header.  What I like: - Name - Description - Input (optional - if describing the input adds value) - Output (optional - if describing the output adds value) - Original SAS version (I've learned this over time - why didn't they just use NODUPOUT?  Oh, cuz it was developed in SAS 9.1) - Original programmer (does he/she still work here?) - Original date - Macros called (kind of a pain to maintain, but a big help for impact analysis if you want to change a macro) - Modification history - Esp. for macros, I also like a complete usage example and usage notes.  If possible I like the usage examples to be working, standalone code, not pseudocode.  Sometimes my macro headers are longer than the macro code! So, here's an example (sorry for the length): /*===================================================================== Program Name            : create_format.sas Purpose                 : Create a format from an input dataset. SAS Version             : SAS 9.1.3 Input Data              : Input Dataset Output Data             : Formats Macros Called           : parmv, kill, nobs Originally Written by   : Scott Bass Date                    : 23MAR2007 Program Version #       : 1.0 ======================================================================= Modification History    : Programmer              : Scott Bass Date                    : 30NOV2012 Change/reason           : Changed to use SPDEWORK library for better                           performance Program Version #       : 1.1 =====================================================================*/ /*---------------------------------------------------------------------- Usage: %create_format(     DATA=WORK.FOO    ,NAME=ABC_FMT ); Create $ABC_FMT format in WORK.FORMATS catalog using WORK.FOO as input. WORK.FOO should contain the variables START and LABEL. ======================================================================= %create_format(     DATA=WORK.FOO    ,NAME=DEF_FMT    ,TYPE=NUM_FORMAT    ,START=START_VALUE    ,END=END_VALUE    ,LABEL=DESCR    ,LIB=LIBRARY    ,CAT=FORMATS ) Create DEF_NUM format in LIBRARY.FORMATS catalog using WORK.FOO as input. WORK.FOO should contain the variables START_VALUE, END_VALUE, and DESCR. ======================================================================= %create_format(     DATA=PERM.DATASET    ,NAME=GHI_CHR    ,TYPE=CHR_INFORMAT    ,START=NAME    ,LABEL=DESCR    ,LIB=LIBRARY    ,CAT=MYFORMAT    ,OTHER=**UNKNOWN**    ,WHERE=DATE gt "01JAN2007"d ) Create GHI_CHR informat in LIBRARY.MYFORMAT catalog using PERM.DATASET as input. Input data not matching any of the ranges should be coded to "**UNKNOWN**". PERM.DATASET should contain the variables NAME and DESCR. Only include records after 01JAN2007 in the format. ======================================================================= %create_format(     DATA=WORK.FOO    ,NAME=cats("MY_",FMT)    ,TYPE=CHR FORMAT ) Create multiple formats from input dataset WORK.FOO. Create the formats in the default catalog (WORK.FORMATS). The name of the format is contained in the variable FMT. We want the format names to begin with MY_. WORK.FOO should contain the variables START and LABEL. ------------------------------------------------------------------------ Notes: If the input dataset contains data for multiple formats (i.e. it contains data for the name of the format), then the format name string should contain a left-parentheses. For example: (VAR):    Would just use VAR as is from the input dataset.    The parentheses are just a trigger to the macro to treat this    as a code fragment. cats("FOO_",VAR):    Would append "FOO_" to the VAR variable in the input dataset. Otherwise &NAME will be used as a hard-coded format name for the entire input dataset. Combining a code fragment for the format name with Other processing will result in only the last format containing Other processing. No error checking is done on the input parameters (other than checking if required parameters are set). The variables for VALUE and LABEL should be character or automatic type conversion will result. Except for START and LABEL, your input dataset should avoid using variable names listed in the attrib statement below.  For example, if your input dataset contained the variable PREFIX, and you used it for the VALUE or LABEL parameters, your data would likely be truncated giving undesired results. The macro issues a warning if your input dataset contains overlapping ranges.  You should pre-process your input dataset to circumvent this warning. ----------------------------------------------------------------------*/ %macro create_format /*--------------------------------------------------------------------- Create a format from an input table ---------------------------------------------------------------------*/ (DATA=                /* Input dataset/view (REQ).                          */                /* Should be a two-level name.                        */ ,LIB=WORK                /* Format library (REQ).                              */                /* Library to which the format is written.            */ ,CAT=FORMATS                /* Format catalog (REQ).                              */                /* Catalog to which the format is written.            */ ,NAME=                /* Format name (REQ).                                 */                /* Either a hard coded name, or a code fragment       */                /* (that could reference a variable in the            */                /* input dataset/view.                                */ ,TYPE=CHR_FORMAT                /* Format type (REQ).                                 */                /* Valid values are CHR_FORMAT, NUM_FORMAT,           */                /* CHR_INFORMAT, NUM_INFORMAT.                        */ ,START=START                /* Starting value for the format (REQ).               */                /* Variable containing the start value for the format */ ,END=                /* Ending value for the format (Opt).                 */                /* Variable containing the end value for the format   */ ,LABEL=LABEL                /* Format label (REQ).                                */                /* Variable containing the label for the format.      */ ,DEFAULT=                /* Specify the default length of the (in)format.      */                /* If blank, PROC FORMAT determines the default       */                /* length based on the maximum label length.          */ ,MIN=                /* Specify a minimum length for the (in)format (Opt). */ ,MAX=                /* Specify a maximum length for the (in)format (Opt). */ ,FUZZ=                /* Specify a fuzz factor for matching values to a     */                /* range (Opt).                                       */ ,OTHER=                /* "Other" processing? (Opt).                         */                /* If non-blank, input data not matching any defined  */                /* ranges will be mapped to the "other" label.        */                /* If value = _MISSING_, value is translated to the   */                /* appropriate label for the format type.             */ ,WHERE=                /* Where processing? (Opt).                           */                /* If non-blank, only data matching the where clause  */                /* will be included in the format.                    */                /* Do not include the "where" keyword.                */ ,DEDUP=Y                /* Dedup the source dataset? (REQ).                   */                /* Default value is YES.  Valid values are:           */                /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */                /* OFF N NO F FALSE and ON Y YES T TRUE               */                /* (case insensitive) are acceptable aliases for      */                /* 0 and 1 respectively.                              */ ); %local macro parmerr other where name type; %let macro = &sysmacroname; %* check input parameters ; %parmv(DATA,         _req=1,_words=1,_case=N)  /* words=1 allows ds options */ %parmv(LIB,          _req=1,_words=0,_case=U) %parmv(CAT,          _req=1,_words=0,_case=U) %parmv(NAME,         _req=1,_words=0,_case=U) %parmv(TYPE,         _req=1,_words=0,_case=U,_val=CHR_FORMAT NUM_FORMAT CHR_INFORMAT NUM_INFORMAT) %parmv(START,        _req=1,_words=0,_case=U) %parmv(END,          _req=0,_words=0,_case=U) %parmv(LABEL,        _req=1,_words=0,_case=U) %parmv(DEFAULT,      _req=0,_words=0,_case=U,_val=POSITIVE) %parmv(MIN,          _req=0,_words=0,_case=U,_val=POSITIVE) %parmv(MAX,          _req=0,_words=0,_case=U,_val=POSITIVE) %parmv(FUZZ,         _req=0,_words=0,_case=U) %parmv(DEDUP,        _req=1,_words=0,_case=U,_val=0 1) %if (&parmerr) %then %goto quit; %let WHERE = %unquote(&WHERE); libname _crtfmt_ spde "%sysfunc(pathname(work))" temp=yes; %* I know I do not need all these attributes now, but I have left them in here ; %* in case additional options are added to the macro in the future ; %let cntlin=_crtfmt_._cntlin_; data &cntlin;    format FMTNAME TYPE START END LABEL; /*    attrib       FMTNAME     length=$32     label="Format name"       START       length=$200    label="Starting value for format"       END         length=$200    label="Ending value for format"       LABEL       length=$5000   label="Format value label"       MIN         length=3       label="Minimum length"       MAX         length=3       label="Maximum length"       DEFAULT     length=3       label="Default length"       LENGTH      length=3       label="Format length"       FUZZ        length=8       label="Fuzz value"       PREFIX      length=$2      label="Prefix characters"       MULT        length=8       label="Multiplier"       FILL        length=$1      label="Fill character"       NOEDIT      length=3       label="Is picture string noedit?"       TYPE        length=$1      label="Type of format"       SEXCL       length=$1      label="Start exclusion"       EEXCL       length=$1      label="End exclusion"       HLO         length=$11     label="Additional information"       DECSEP      length=$1      label="Decimal separator"       DIGSEP      length=$1      label="Three-digit separator"       DATATYPE    length=$8      label="Date/time/datetime?"       LANGUAGE    length=$8      label="Language for date strings"    ;    if _n_=1 then call missing(of _all_); */    set &DATA end=_last_;    %if (%superq(WHERE) ne %str() ) %then %do;       where &WHERE;    %end;    %* if the format name or label contains a left parentheses, assume it is a code fragment ;    %* otherwise it is a hard coded format name or label ;    %if (%index(%superq(NAME),%str(%())) %then       %let NAME = &NAME;    %else       %let NAME = "&NAME";    %if (&TYPE = CHR_FORMAT) %then       %let type = C;    %else    %if (&TYPE = NUM_FORMAT) %then       %let type = N;    %else    %if (&TYPE = CHR_INFORMAT) %then       %let type = J;    %else    %if (&TYPE = NUM_INFORMAT) %then       %let type = I;    %if (%superq(END) eq ) %then %let END = &START;    FMTNAME  = &NAME;    TYPE     = "&type";    START    = &START;    END      = &END;    LABEL    = &LABEL;    %if (&default ne ) %then %do;    DEFAULT  = &default;    %end;    %if (&min ne ) %then %do;    MIN      = &min;    %end;    %if (&max ne ) %then %do;    MAX      = &max;    %end;    %if (&fuzz ne ) %then %do;    FUZZ     = &fuzz;    %end;    output;    %if (%superq(OTHER) ne ) %then %do;       if (_last_) then do;          call missing(START);          call missing(END);          %if (%qupcase(%superq(OTHER)) eq _MISSING_) %then %do;             %if (%sysfunc(indexc(&TYPE,CNJ))) %then %do;                LABEL = " ";             %end;             %else %do;                LABEL = .;             %end;          %end;          %else %do;             %if (%sysfunc(indexc(&TYPE,CNJ))) %then %do;                LABEL = "&OTHER";             %end;             %else %do;                LABEL = &OTHER;             %end;          %end;          HLO   = "O";          output;       end;    %end;    /*    keep       FMTNAME       START       END       LABEL       MIN       MAX       DEFAULT       LENGTH       FUZZ       PREFIX       MULT       FILL       NOEDIT       TYPE       SEXCL       EEXCL       HLO       DECSEP       DIGSEP       DATATYPE       LANGUAGE    ;    */ run; %if (&dedup) %then %do;   %let cntlin=_crtfmt_._cntlin_nodup_;   %* remove duplicate ranges from input dataset ;   proc sort data=_crtfmt_._cntlin_ out=&cntlin dupout=_crtfmt_._cntlin_dupout_ nodupkey;      by fmtname start;   run;   %* print message if duplicate observations were deleted ;   %if (%nobs(_crtfmt_._cntlin_dupout_) gt 0) %then %do;      %* put %str(WAR)NING:  Duplicate ranges were detected in the &DATA dataset.;      %put %str(NO)TE:  Duplicate ranges were detected in the &DATA dataset.;      %* Uncomment the below line if calling this macro from a DIS job ;      %*rcSet(4);   %end; %end; %* create format(s) ; proc format cntlin=&cntlin lib=&LIB..&CAT; quit; %quit: %mend; /******* END OF FILE *******/ ... View more

Why don't you acknowledge that "...it's a mess and gives you a headache", and rearchitect your approach? I'm not saying the below is the best approach - I threw it together in a hurry - but perhaps it gives you some ideas... options mprint; * first approach - "wide" dataset ; %macro test(metadata=sashelp.cars,make=,model=);    data _null_;       set &metadata;       where strip(make)="&make" and strip(model)="&model";       length dummy $1 name $32;       call missing(dummy);       do while (1);          call vnext(name);          if (name in ("model","type")) then continue;          if (name="dummy") then leave;          call symputx("__"||name,vvaluex(name),"L");       end;    run;    %printmvars %mend; %macro printmvars;    proc sql;       select name,value from dictionary.macros where name like "^__%" escape "^";    quit; %mend; %test(make=Acura, model=MDX); %test(make=Cadillac, model=Escalade); * the choice of swinging golfers everywhere ; * second approach - "tall" dataset ; data metadata;    length macroname name $32 value $200;    input macroname name value;    datalines; TEST1 FOO   1 TEST1 BAR   2 TEST1 BLAH  3 TEST2 WHATEVER 4 TEST2 IMDONE   5 ; run; %macro createmvars(metadata=metadata,mname=);    %let mname=%upcase(&mname);    data _null_;       set &metadata;       where upcase(macroname)="&mname";       call symputx(name,value,"L");    run;    %&mname %mend; %macro test1;    proc sql;       select * from dictionary.macros where scope in ("CREATEMVARS","&sysmacroname");    quit;    %put &foo &bar &blah; %mend; %macro test2;    proc sql;       select * from dictionary.macros where scope in ("CREATEMVARS","&sysmacroname");    quit;    %put &whatever &imdone; %mend; %createmvars(mname=test1); %createmvars(mname=test2); ... View more

Bump... Even if the answer is "can't be done", it would be nice to 1) know that, and 2) why not? 😉 .  I'd like to get back to the end user with some kind of answer... Any little birdies lurking?  This seems too trivial for a SAS Support ticket. ... View more

OK, you say: The user can pass one parameter var1 with one or more values, OR He can pass multiple parameters var1 var2 ... with one or more values to var1 / var2.  (emphasis added, what's with the ellipsis?) Sorry for being dense, but let me reword that 2nd statement to make sure I've got it right: He can pass two parameters var1 var2, with one or more values to var1 and var2. You then say the var2 parameter has precedence over the var1 parameter, if defined (non-blank). Assuming this is correct, does this work for you? Go here:  SAS Macros - by Richard A. DeVenezia - seplist.sas, copy %seplist, and compile the macro.  Then submit the following code: %macro sasphile(var1=, var2=);   %* var1 and var2 should be space separated lists ;   %local filter;   %if (%superq(var2) ne ) %then %do;     %let filter = age in (%seplist(&var2));   %end;   %else   %if (%superq(var1) ne ) %then %do;     %let var1=%upcase(&var1);     %let filter = upcase(name) in (%seplist(&var1,nest=qq));     %let filter = %unquote(&filter);   %end;   proc sql;     create table test as       select * from sashelp.class       %if (%superq(filter) ne ) %then %do;       where &filter       %end;       ;   quit;   proc print;   run; %mend; options mprint; %sasphile(var1=alfred henry joyce robert) %sasphile(var1=alfred henry joyce robert, var2=12 14) Further comments: 1)  %seplist returns a macro quoted string (see the qscan function in the macro).  You can either change the bottom of the macro from &emit to %unquote(&emit), or else unquote the results in your calling code.  (If you ever write a macro, view the mprint results in the log, go "I swear this code is correct", and start pulling your hair out, check your macro quoting ;-).  To see what I mean, comment out or delete the line %let filter = %unquote(&filter); and resubmit with MPRINT active. 2)  These lines could be combined:     %let var1=%upcase(&var1);     %let filter = upcase(name) in (%seplist(&var1,nest=qq));     %let filter = %unquote(&filter); In fact, the %upcase function does implicit macro unquoting, so this would work, although it's perhaps less clear: %let filter = upcase(name) in (%upcase(%seplist(&var1,nest=qq))); 3)  The %superq function calls are probably unnecessary, but is defensive coding.  Say you DID have the names AND and OR in your dataset (a contrived example I know): data class;   set sashelp.class end=eof;   output;   if eof then do;     name="AND";output;     name="OR";output;   end; run; %sasphile(var1=alfred henry joyce robert and or) The current macro works, even if your where clause contains reserved macro operators.  Now remove the %superq function calls, i.e. %if (&var2 ne ) and resubmit. Hope this helps... Scott ... View more