Nikolaj Veje,Danish National Police

1. Management decisions can be aligned with strategy.
2. Resources can be allocated to activities with a high priority.
3. Resources can then be made available for new priorities.

When clients can't find the connection

libname mydata2 META library="metaref";
proc datasets nolist nodetails;
   contents data=mydata2._all_;
run;

options metaserver="mymetadataserver" metaport=8561 metauser="myuserid" metapass="mypasswd" metarepository="Foundation";
libname mydata2 META library="metaref";
proc datasets nolist nodetails;
   contents data=mydata2._all_;
run;

So what?  Where is the “best practice” in that?!?

Encoding a password for an autoexec file

1. Encode your password and save the resulting password in a file.
2. Create a file that reads the password and sets the options statement.
3. Include that file in your program.

Step 1.

filename pwfile '~/pwencode';
proc pwencode in='mypassword' out=pwfile;
run;

Step 2.

/* Change these values based on your organization */
%let myuserid=gnelson;
%let myserver=mymetadataservername;
/* Read the password file */
filename pwfile '~/pwencode';
data _NULL_;
   infile pwfile obs=1 length=l;
   input @;
   input @1 line $varying1024. l;
   call symput('dbpass',substr(line,1,l));
run;
/* Specify connection options. Change as needed for your installation */
options metaserver="&MYSERVER" metaport=8561 metauser="&MYUSERID" metapass="&DBPASS" metarepository="Foundation"

Step 3.

%include '~/dbpass.sas';
libname mydata2 META library="metaref";
proc datasets nolist nodetails;
   contents data=mydata2._all_;
run;

Retrieving connection details: alternatives

Nevertheless, many SAS customers use macro constructs as part of their daily SAS programming tasks, and that practice often continues when they write SAS/IML programmers. A customer recently asked a question about the macro language that required knowledge of the way that macro variables are handled within a SAS/IML loop. This post shares my response.

Here's the crux of the customer's question. Run the following SAS/IML program and see if you can understand why it behaves as it does:

proc iml;
i = 7;
call symputx("j", i);    /* 1. Put value of i into macro variable j */
y1 = &j;                 /* 2. Assign y1 the value of &j            */
print y1;                /* success! */

y = j(1,4,.);
do i = 1 to ncol(y);     /* 3. Start processing the DO block of statements */
   call symputx("j", i); /* 4. Put value of i into macro variable j */
   y[i] = &j;            /* 5. Hmmmm, what does this do inside the loop? */
end;
print y;                 /* Not what you might expect? */

As you can see from the output, the first use of the macro variable (outside the DO loop), works as expected. But the second does not. The customer wanted to know why the elements of y are not set to 1, 2, 3, 4 within the loop.

The key point to remember about macro variables is that SAS code never sees them. Macro variables are evaluated by the macro preprocessor at parse time, not at run time. The SAS/IML code never sees &j, only the constant value that the preprocessor substitutes for &j.

It is also important to remember that PROC IML is an interactive procedure. (The "I" in IML stands for interactive!) Each statement or block of statements is parsed as it is encountered, as opposed to the DATA step, which parses the entire program before beginning execution.

Let's examine the program step-by-step to understand why the first construct works but the second does not. The following steps refer to the numbers in the program comments:

1. The value of the SAS/IML scalar i is copied (as text) into the macro variable j.
2. The statement is encountered. The value of the macro variable j is substituted by the macro preprocesser. Then the statement is executed. The SAS/IML variable y1 is assigned to the value 7.
3. A DO loop is encountered by the SAS/IML parser. The parser finds the matching END statement and proceeds to parse the entire body of the loop in order to check for syntax errors. This parsing phase occurs exactly one time. Because the block of statements contain a macro variable, the macro preprocessor substitutes the value of the macro variable j, which is 7.
4. For each iteration, the value of the SAS/IML scalar i is copied (as text) into the macro variable j.
5. For each iteration, the ith element of the y vector is assigned the value 7. In particular, this statement does not contain a reference to the macro varible j.

To the casual reader of the program, it looks like &j will have a different value during each step of the iteration. But but it doesn't. The expression &j is resolved at parse time. SAS/IML parses the entire body of the DO loop once, before any execution occurs, and at parse time the expression &j is 7.

There is a way to get what the customer wants. The SYMGET function retrieves the value of a macro variable at run time. Therefore the following statements fill the vector y with the values 1 through 4:

do i = 1 to ncol(y);
   call symputx("j", i);
   y[i] = num(symget("j"));  /* get macro value at run time */
end;
print y;                     /* Yes! This is what we want! */

For me, this blog post emphasizes three facts:

• Always remember that macro substitution is done by a preprocessor, which operates at parse time.
• The SAS/IML language parses an entire block of statements (between the DO and END statements) one time before executing the block.
• Mixing macro code and SAS/IML statements can be confusing and hard to debug. When you have the option, use SAS/IML language features instead of relying on macro language constructs.

proc sgplot data=testcols;
  title 'Sum and Percent by Week and Operation';
  vbar week / response=sum fillattrs=graphdatadefault nostatlabel;
  vline week /  response=A lineattrs=(thickness=5 pattern=solid) nostatlabel y2axis;
  vline week /  response=B lineattrs=(thickness=5 pattern=solid) nostatlabel y2axis;
  vline week /  response=C lineattrs=(thickness=5 pattern=solid) nostatlabel y2axis;
  vline week /  response=D lineattrs=(thickness=5 pattern=solid) nostatlabel y2axis;
  vline week /  response=E lineattrs=(thickness=5 pattern=solid) nostatlabel y2axis;
  vline week /  response=F lineattrs=(thickness=5 pattern=solid) nostatlabel y2axis;
  xaxis display=(nolabel);
  yaxis offsetmin=0  display=(nolabel) ;
  y2axis offsetmin=0 display=(nolabel);
  run;

• We have used a VBAR statement to display sum x week.
• We have used multiple VLINE statements to display each % by week.
• We have set offsetmin=0 for both Y and Y2 axis.  This ensures the bars start from the x axis.
• NOSTATLABEL prevents the addition of the statistics to the labels and legends.
• The Y and Y2 axis ticks are not aligned, so it is hard to draw grid lines.

proc sgplot data=test noautolegend;
  title 'Sum and Percent by Week and Operation';
  vbar week / group=operation response=sum_week fillattrs=graphdatadefault
       nostatlabel transparency=0.2 dataskin=gloss;
  vline week / group=operation response=percent y2axis
        lineattrs=(thickness=5 pattern=solid) nostatlabel curvelabel;
  xaxis display=(nolabel);
  yaxis offsetmin=0 offsetmax=0.1 values=(0 to 8000 by 2000)
        display=(nolabel) grid;
  y2axis  offsetmin=0 offsetmax=0.1 values=(0 to 0.4 by 0.1)
        display=(nolabel);
  run;

• Troubleshoot quality problems
• Improve manufacturing yield
• Improve the capability of your measurement system
• Control complex manufacturing processes