tag:blogger.com,1999:blog-79548515610187600252024-02-02T07:54:19.617-08:00KanSAS CodeCode example from an advanced SAS certified professional living in KansasUnknownnoreply@blogger.comBlogger113125tag:blogger.com,1999:blog-7954851561018760025.post-12231803090987044192022-08-28T17:33:00.003-07:002022-08-29T05:08:26.477-07:00A Robust Histogram<p>I wanted to create a robust histogram to display the response time of a web site. To make a histogram you need to create your own statgraph using the <a href="https://documentation.sas.com/api/docsets/grstatug/9.4/content/grstatug.pdf?locale=en" target="_blank">Graphic Template Language (GTL)</a>. This requires a lot of research to see what is needed to make it all happen. Below is an image of the end result.</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCtctZJNlda_4kKJeKTXh_T93m-CQ_fhsdehcX5jsDQ8M1kMZOf5dsA-kMGaNDP8SBTYnyBPQ6VWhD-OZ2FsczfCcQ8LHNBp0z3VtlWUKoU9jXcKlMiTEIsjx6HBApHDNEew7cQj9R3ZcUlASNdx22tr0czCu2uR1zttbyrNhWqg2uHOTHChtuX6f0/s1600/histogram.jpg" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" data-original-height="436" data-original-width="580" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCtctZJNlda_4kKJeKTXh_T93m-CQ_fhsdehcX5jsDQ8M1kMZOf5dsA-kMGaNDP8SBTYnyBPQ6VWhD-OZ2FsczfCcQ8LHNBp0z3VtlWUKoU9jXcKlMiTEIsjx6HBApHDNEew7cQj9R3ZcUlASNdx22tr0czCu2uR1zttbyrNhWqg2uHOTHChtuX6f0/s1600/histogram.jpg"/></a></div>
<p>I received some excellent assistance from Lelia at SAS technical support to add the percent sign (%) on the Y axis. This was accomplished by creating a custom format then assigning it to tickvalueformat= option inside linearopts=().</p>
<p>You will see within the proc template code that I am using a vertical reference line to denote the 2.5 second boundary. Each of the bars cover an area between 0.5 above and below the center point. For example, the value 2 has a range of 1.5 to 2.5.</p>
<p>A bunch of summary statistics are handled in GTL by using the eval() function. Below the histogram is a fringeplot that shows the activity of hits by time period. A normal distribution curve was added using the densityplot syntax. Under the fringeplot is a horizontal boxplot that shows the spread of the data in a different fashion than the histogram.</p>
<p>The frequency procedure was used to obtain the percentages for each bar in the display. Here is the SAS data set that was created from the proc freq call.</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg77cPccuH7NBaDOSZd2bmdYUtK5oUSjBadmN1xr2cLXOYJ8G3-yARz6sRBLVijXnP_NRHcjgKMUi2dYttk0pepRSpxsb6b3dAsY34F9J0YrHXXebyDllaWSBcyu00r9AJ5Ic6IHKfT_5tlqw354W5JVOyoY3en0BXJL8OlMX4JaXCKvWsB1LM45Rtt/s670/freq.jpg" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="1" width="600" data-original-height="242" data-original-width="670" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg77cPccuH7NBaDOSZd2bmdYUtK5oUSjBadmN1xr2cLXOYJ8G3-yARz6sRBLVijXnP_NRHcjgKMUi2dYttk0pepRSpxsb6b3dAsY34F9J0YrHXXebyDllaWSBcyu00r9AJ5Ic6IHKfT_5tlqw354W5JVOyoY3en0BXJL8OlMX4JaXCKvWsB1LM45Rtt/s600/freq.jpg"/></a></div>
<p>The final piece I wanted was to display the actual bin values above the bars in the histogram. There are no options to do this using GTL but SAS does support the inclusion of an annotated (sganno) data set. With help from SAS tech support I was able to accomplish that last wish list item resulting in what I call a very robust histogram.</p>
<p>I used a random function (call streaminit with rand()) to create simulated data and suppressed values that exceeded 1.75. The rounded duration value was used with proc freq to drive the annoated data set to populate the values above the bars and the reference line.</p>
<p>The proc template code is very much reusable and is used by proc sgrender to create the output. You will notice that I am writing to the WORK folder via ODS HTML5.</p>
<pre>
proc format;
picture mypct (round)
low - high='009%';
run;
proc template;
define statgraph distribution;
dynamic
VAR
VARLABEL
TITLE
NORMAL
fmt
;
mvar pct2secs;
begingraph;
entrytitle TITLE;
layout lattice /
columns = 1
rows = 2
rowgutter = 2px
rowweights = (.9 .1)
columndatarange = union
;
columnaxes;
columnaxis /
label = VARLABEL
display = (ticks tickvalues label)
type = linear
;
endcolumnaxes;
layout overlay /
yaxisopts = (
offsetmin = .035
offsetmax = .065
griddisplay = on
linearopts = (tickvalueformat = mypct4.)
)
xaxisopts = (label = "Duration in Seconds");
referenceline x = 2.5 /
lineattrs = (color = red pattern = dash)
curvelabel = pct2secs
curvelabellocation = outside
;
layout gridded /
columns = 2
border = true
autoalign = (topleft topright)
;
entry halign = left "Nobs";
entry halign = right eval(strip(put(n(VAR), comma12.)));
entry halign = left "Min";
entry halign = right eval(strip(putn(min(VAR), '12.3')));
entry halign = left "Q1";
entry halign = right eval(strip(putn(q1(VAR), '12.3')));
entry halign = left "Median";
entry halign = right eval(strip(putn(median(VAR), '12.3')));
entry halign = left "Mean";
entry halign = right eval(strip(putn(mean(VAR), '12.3')));
entry halign = left "Q3";
entry halign = right eval(strip(putn(q3(VAR), '12.3')));
entry halign = left "Max";
entry halign = right eval(strip(putn(max(VAR), '12.3')));
entry halign = left "StdDev";
entry halign = right eval(strip(putn(stddev(VAR), '12.3')));
entry halign = left "IQR";
entry halign = right eval(strip(putn(qrange(VAR), '12.3')));
endlayout;
histogram VAR /
scale = percent
binwidth = 1
dataskin = gloss
;
annotate / id='label';
if (exists(NORMAL))
densityplot VAR /
normal()
name = 'norm'
legendlabel = 'Normal'
;
endif;
fringeplot VAR / datatransparency = .7;
discretelegend "norm" "kern" /
location = inside
across = 1
autoalign = (topright topleft)
opaque = true
;
endlayout;
boxplot y = VAR / orient = horizontal;
endlayout;
endgraph;
end;
run;
data hitsinseconds;
call streaminit(123); /* set random number seed */
do _n_ = 1 to 2000;
duration = round(7 * rand("Uniform"), .001);
if duration > 1.75 then do;
if rand("Uniform") > .05 then continue;
end;
Seconds = round(duration, 1);
output;
end;
run;
proc freq data = hitsinseconds noprint;
table seconds / out = hitfreq outcum;
run;
data sganno;
retain id 'label';
set hitfreq;
drawspace = 'datavalue';
function = 'text';
x1 = seconds;
y1 = percent;
label = cats(put(percent, 7.2), '%');
textcolor = 'black';
textsize = 8;
anchor = 'bottom';
if seconds = 2 then call symputx('pct2secs', cats(put(cum_pct, 8.), '%'));
run;
ods listing close;
ods graphics on / width = 640px height = 480px;
ods html5
path = "%sysfunc(pathname(work))"
file = "histogram.html"
style = seaside
;
title;
proc sgrender
data = hitsinseconds
template = distribution
sganno = sganno
;
dynamic
var = "duration"
varlabel = "Time in Seconds"
normal = "yes"
title = "&pct2secs of hits were under 2.5 seconds"
;
format duration 8.1;
run;
ods html close;
</pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-34866130572938299942022-06-20T07:39:00.000-07:002022-06-20T07:39:50.528-07:00When a problem comes along, you must whip it!<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXW62Qf6GANqADJnQUEoQyFABiNiOOYGyGEExIp69PCSNlvuz6ImXQf2_l__yTuFRQYvJBUda70Y_cQ_0gkVYUFuCqGKd7DMQsdYpBR3CJvw9fyy6FcP-FJ3V8l-fJyVUlzSrSeq1zEy9hSyXoGwtFNJM-4Nm9DncdFn9_p_T4nNR6DfynbPdkMwRy/s400/whipit.gif" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" width="320" data-original-height="300" data-original-width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXW62Qf6GANqADJnQUEoQyFABiNiOOYGyGEExIp69PCSNlvuz6ImXQf2_l__yTuFRQYvJBUda70Y_cQ_0gkVYUFuCqGKd7DMQsdYpBR3CJvw9fyy6FcP-FJ3V8l-fJyVUlzSrSeq1zEy9hSyXoGwtFNJM-4Nm9DncdFn9_p_T4nNR6DfynbPdkMwRy/s320/whipit.gif"/></a></div>
<p>While I have used the SAS <a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/lefunctionsref/p00ab6ey29t2i8n1ihel88tqtga9.htm" target="_blank">FIND()</a> function frequently, that is not the case for the similarly named <a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/lefunctionsref/p16rdsa30vmm43n1ej4936nwa01t.htm" target="_blank">FINDW()</a> (find word) function. Taking advice from the 1980s band Devo, a problem came along and I was ready to whip it, whip it good!</p>
<p>A <a href="https://en.wikipedia.org/wiki/Pangram" target="_blank">pangram</a> is a unique sentence that contains all the leters of the alphabet at least once. The name comes from the Greek root words pan, meaning "all," and gram, meaning "something written or recorded". The best-known English pangram is "<b>The quick brown fox jumps over the lazy dog</b>" and that is what I will use to test the issue.</p>
<p>The FIND() function returns the starting position of the substring of characters so it can find both Quick and Qui. It is important to note that the 3rd parameter in FIND that uses an 'i' modifier to ignore the case of the characters - upper and lower are treated the same.</p>
<p>Now that we know how the FIND() function works, you would think that FINDW() would do the same - that is the 3rd parameter just needs a 'i' to ignore the case and it will work on full words not a substring. That is not the case for FINDW(), see the differnce between a_findw and a_findw2 that can find the word 'Quick'.</p>
<p>There are times I want an exact match vs a substring match and now I can see the differences - problem whiped - err solved.</p>
<pre>
data test;
str = "The quick brown fox jumps over the lazy dog";
a_find = find(str, "Quick", 'i'); /* found at position 5 */
a_findw = findw(str, "Quick", 'i'); /* not found */
a_findw2 = findw(str, "Quick", ' ', 'i'); /* found at position 5 */
b_find = find(str, 'Qui', 'i'); /* found at position 5 */
b_findw2 = findw(str, 'Qui', ' ', 'i'); /* not found - no word named Qui */
run;
</pre>
<p>Should you think this post has gone to the dogs, please consider just as in SAS there are many ways to solve a problem there too are alternate ways to whip it, whippet good!</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBmSk8WwLTTYtsu3b4qJPH3YmKdciW4ZAaiWiAUutR-VMM09UMcuqBmDVUqiiadGX0itXgtTviZL-2pW8w59FCT13ioWIrZXvbWVxIaYVcRI6xeIKoWtg6StvnlgZnGLCSpwOp09uSMSp2OcqVBOFgWx8_FKQ_nJSlRfAItCS9ho-GTnOmbCka-6cJ/s490/whippet.jpg" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" width="320" data-original-height="312" data-original-width="490" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBmSk8WwLTTYtsu3b4qJPH3YmKdciW4ZAaiWiAUutR-VMM09UMcuqBmDVUqiiadGX0itXgtTviZL-2pW8w59FCT13ioWIrZXvbWVxIaYVcRI6xeIKoWtg6StvnlgZnGLCSpwOp09uSMSp2OcqVBOFgWx8_FKQ_nJSlRfAItCS9ho-GTnOmbCka-6cJ/s320/whippet.jpg"/></a></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-35180721888690058072022-05-15T12:31:00.005-07:002022-06-02T06:22:12.398-07:00If only I had an ArrayModeling data sets often have binary flag variable to indicate if a condition is true or false. Sometimes those indicators need to be collapsed or recoded into a single value based on conditions. See the below code for one technique to handle this.
<pre>
if s_del30postscratch_ind = 1 then curr_delinquency = "D30";
else if s_del60postscratch_ind = 1 then curr_delinquency = "D60";
else if s_del90postscratch_ind = 1 then curr_delinquency = "D90";
else if s_del120postscratch_ind = 1 then curr_delinquency = "D120";
else if s_del150postscratch_ind = 1 then curr_delinquency = "D150";
else if s_del180postscratch_ind = 1 then curr_delinquency = "D180+";
else curr_delinquency = "Current";
</pre>
An alternative way to do the same thing in SAS using two arrays is as follows:
<pre>
array avars s_del30postscratch_ind s_del60postscratch_ind s_del90postscratch_ind
s_del120postscratch_ind s_del180postscratch_ind;
array anames[5] $8 _temporary_ ('D30', 'D60', 'D90', 'D120', 'D180+');
do _n_ = 1 to dim(avars);
if avars[_n_] = 1 then curr_delinquency = anames[_n_];
end;
if sum(of avars[*]) = 0 then curr_delinquency = 'Current';
</pre>
Here is the entire array technique complete with some sample data. The temporary array has the same number of elements so the relative offset matches and makes this a better, more eloquent technique that can be expanded to easily support more if statements if that is what you encounter. Notice the use of the double dash (--) to specify the start and stop columns to process that can be used as a shortcut. Just a different way to do the same thing.
<pre>
data x;
length curr_delinquency $8;
input s_del30postscratch_ind s_del60postscratch_ind s_del90postscratch_ind
s_del120postscratch_ind s_del180postscratch_ind;
array avars s_del30postscratch_ind -- s_del180postscratch_ind;
array anames[5] $8 _temporary_ ('D30', 'D60', 'D90', 'D120', 'D180+');
do _n_ = 1 to dim(avars);
if avars[_n_] = 1 then curr_delinquency = anames[_n_];
end;
if sum(of avars[*]) = 0 then curr_delinquency = 'Current';
datalines;
1 0 0 0 0
0 1 0 0 0
0 0 1 0 0
0 0 0 1 0
0 0 0 0 1
1 1 1 1 1
0 0 0 0 0
;
run;
</pre>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7jVfbqHduS1gpX7Pje15YB6VlqBOuaU92fUCNpxFgSX82O8Y3DBNzTWwen2Enva5HyruQ_ILpiY8na5Mv602eGOgju9FJ-AZWyno6iiRRrYgUHo5ay-ERkwwBATziCIFnfVGNtagFToje8TOWet9b8ZGek8dbujOiRHBTSWiLYF1MCrG0nOoNirIO/s248/array_if.png" style="display: block; padding: 1em 0; text-align: center; clear: left; float: left;"><img alt="" border="1" height="320" data-original-height="248" data-original-width="223" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7jVfbqHduS1gpX7Pje15YB6VlqBOuaU92fUCNpxFgSX82O8Y3DBNzTWwen2Enva5HyruQ_ILpiY8na5Mv602eGOgju9FJ-AZWyno6iiRRrYgUHo5ay-ERkwwBATziCIFnfVGNtagFToje8TOWet9b8ZGek8dbujOiRHBTSWiLYF1MCrG0nOoNirIO/s320/array_if.png" /></a></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-37430113119360028852021-08-27T08:44:00.000-07:002021-08-27T08:44:55.832-07:00Last Column of DSN<p>You can use the double dash ( -- ) shorthand to drop a list of columns in a data set based on names. I needed to do this for a list of data sets starting at a certain column to the very last column, but the last column name changed between data sets.</p>
<p>The below macro utilizes low level SAS functions to return the name of the last column. So now I can do something like this:</p>
<pre>
data x;
set sashelp.class(drop = age -- %lastcolumn(sashelp.class));
run;
%macro lastcolumn(dsn);
%local dsid retval;
%let dsid = %sysfunc(open(&dsn));
%if &dsid %then %do;
%let retval = %sysfunc(varname(&dsid, %sysfunc(attrn(&dsid, nvars))));
%let dsid = %sysfunc(close(&dsid));
%end;
&retval
%mend;
</pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-60325014441492574422021-08-19T15:02:00.001-07:002021-08-19T15:12:44.004-07:00My Snippets - SQL Snapshot<p>Now that SAS Studio is becomming more prevalant, I am moving away from Enterprise Guide into SAS Studio. A nice feature of SAS Studio is the ability to add your own code snippets.</p>
<p>While working on a project, I often find myself having to trace thru a multitude of data sets and track the number of rows and columns at each step. I typically keep all those data sets in the WORK library. I have created the below code snippet to support that type of activity.</p>
<p>The steps to save this snippet are to write the SQL statement as shown below then either right mouse click and select "Add to My Snippets" or click on the icon circled in red. Provide a name then click the Save button. Now you can easily recall that code snippet by clicking on Snippets, My Snippets, snap (short for snapshot).</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIpUW3meZMutwX3xQIMSag8odAbuay_kCDgp3NMvSDqZEREAOsfsL6_fvtM6lEjWGbifPUNAOKLy7LKb7Q5Cf7sLT0Ct1D347GovCM7KAaJCh7T45Q3Ea5gGp-qA6F7toD4MT9qzS_qlM/s0/snap_snippet.png" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" data-original-height="466" data-original-width="620" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIpUW3meZMutwX3xQIMSag8odAbuay_kCDgp3NMvSDqZEREAOsfsL6_fvtM6lEjWGbifPUNAOKLy7LKb7Q5Cf7sLT0Ct1D347GovCM7KAaJCh7T45Q3Ea5gGp-qA6F7toD4MT9qzS_qlM/s0/snap_snippet.png"/></a></div>
<p>Below is an example output from running the snap snippet. Notice that the output is sorted by the last modified date so it follows the flow of my program.</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeOmqlBTMQXHj1wmCWpoGetoNqfgc2GQjfUeGS2hPC4W9JVdDHOycxSgVwPfAQ-Ep4jBkvldsAYMgIKZUtPdQrgXkS2Aw70-umBZIjIjqbeLJlOKHxxCHQlKnZhTgbA0_3ovDJ7UBYuX4/s0/image001.png" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" data-original-height="268" data-original-width="608" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeOmqlBTMQXHj1wmCWpoGetoNqfgc2GQjfUeGS2hPC4W9JVdDHOycxSgVwPfAQ-Ep4jBkvldsAYMgIKZUtPdQrgXkS2Aw70-umBZIjIjqbeLJlOKHxxCHQlKnZhTgbA0_3ovDJ7UBYuX4/s0/image001.png"/></a></div>Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-7954851561018760025.post-57857999881261006612021-07-15T11:42:00.003-07:002021-07-15T18:50:39.252-07:00Free Beer<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSJbiPexu6hcG4js9S9vAwgB6G7ygB3Nabvs90tftGEM0Yzor5PWC9RkazR1pLfBTaT1K3n3XE2S41GIs5UzWU5zfOqmlOFvTfDs4BbCrbDPJaIPACCVmzmQtgZLLNjm2BQ9wmPfvr17Q/s0/freebeer.png" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" data-original-height="476" data-original-width="627" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSJbiPexu6hcG4js9S9vAwgB6G7ygB3Nabvs90tftGEM0Yzor5PWC9RkazR1pLfBTaT1K3n3XE2S41GIs5UzWU5zfOqmlOFvTfDs4BbCrbDPJaIPACCVmzmQtgZLLNjm2BQ9wmPfvr17Q/s0/freebeer.png"/></a></div>
<p>Ok, so maybe the title got you a bit excited but how do you feel about free SODA as in SAS OnDemand for Academics? SAS OnDemand for Academics is a free cloud based version of SAS that uses SAS Studio in your web browser as an editor. This means that there is no software to download and install and that with your credentials you can use SAS anywhere you have an internet connection.</p>
<p>To get started you just need to create a SAS profile for a free account with SAS on <a href="https://welcome.oda.sas.com/home">this page</a>. If you already have a profile you can use your email address and password to logon. Otherwise, click on the <i>Don't have a SAS Profile</i> link shown in the below image. You will need to verify that your email is valid by acknowledging a verfication email.</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglztsa95iY_pWoNWRWla1c_15mYj3RMST2lgrSN2vqXB7lroJ-H7r0wuoCH371tpkAWRcsLaPI-I-7JxXpbJWmL4KdjQwLyir735q5GXzRPGHj4VNBsU7q-GOi0snplhOLswlXLJ2jwuA/s0/soda_2.png" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" data-original-height="466" data-original-width="410" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglztsa95iY_pWoNWRWla1c_15mYj3RMST2lgrSN2vqXB7lroJ-H7r0wuoCH371tpkAWRcsLaPI-I-7JxXpbJWmL4KdjQwLyir735q5GXzRPGHj4VNBsU7q-GOi0snplhOLswlXLJ2jwuA/s0/soda_2.png"/></a></div>
<p>After you have logged in, you will see a screen similar to the image below. Notice in the lower right that there is a progress bar used to display how much of your 5GB of free disk space you have used. To get started, click on the SAS Studio link circled in red below.</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1v1UX7UgnSjTLJy8I7YsE6USe6Tg775Pmz_z2H5vIFFuLv1HXdrCB4CuO00APRDzozAE4MlR3xOpkCnZ4cPeQkg629YRvDrP-4w87KwmIJ16OeSkbRp7e9m0uTvIOEnG_XgdinWuAGUI/s1145/soda_3.png" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" width="600" data-original-height="422" data-original-width="1145" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1v1UX7UgnSjTLJy8I7YsE6USe6Tg775Pmz_z2H5vIFFuLv1HXdrCB4CuO00APRDzozAE4MlR3xOpkCnZ4cPeQkg629YRvDrP-4w87KwmIJ16OeSkbRp7e9m0uTvIOEnG_XgdinWuAGUI/s600/soda_3.png"/></a></div>
<p>Once you start SAS Studio, you will see an image similar to that shown below. There is way too much to cover with a blank editor so suggest users look at the <a href="https://documentation.sas.com/doc/en/sasstudiocdc/3.8/webeditorcdc/sasstudioov/aboutthedoc.htm?homeOnFail">SAS Studio documentation</a> for more information.</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgPJd_Fwe1-gDp88CE48NSGFrJ3CZuGeJbRmGDEhSfyOz4Zkla8Wx_16o4sZUPx39U63Mm-FFmVlxpsf2Cc3wxcgmtPO_Z1lCND7zS7GVZrdpaX8nc_jHJDPCnT75ZkgXNBNXMaIOeO1Q/s1059/soda_4.png" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" width="600" data-original-height="607" data-original-width="1059" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgPJd_Fwe1-gDp88CE48NSGFrJ3CZuGeJbRmGDEhSfyOz4Zkla8Wx_16o4sZUPx39U63Mm-FFmVlxpsf2Cc3wxcgmtPO_Z1lCND7zS7GVZrdpaX8nc_jHJDPCnT75ZkgXNBNXMaIOeO1Q/s600/soda_4.png"/></a></div>
<p>One quick example is to right click on <b>Files (Home)</b> under <b>Server Files and Folders</b> and create a new folder (I called mine data). Next click on that new folder then on the <b>Upload icon</b> (or right mouse click, <b>Upload Files...</b>). Now that your file is in the cloud, right mouse click and select Import Data. Follow the wizard and if that was an Excel file the data just got loaded into a SAS data set in the cloud. From there do whatever analysis you desire usign the familiar SAS syntax.</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFrm8ONI8oW5MsplsV5myw1_6OBYMR2cpKHv8AbYu7YlCDeoWVqE12a3RwPpLY5rDxi4X7G5ko_ZKsPqcsnmvVAhJ9cQ_d6WFwvx2PSVpdJ5MCmo5WFK7XPWaJ60BgtgDXkHlinraelso/s871/soda_5.png" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" width="600" data-original-height="447" data-original-width="871" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFrm8ONI8oW5MsplsV5myw1_6OBYMR2cpKHv8AbYu7YlCDeoWVqE12a3RwPpLY5rDxi4X7G5ko_ZKsPqcsnmvVAhJ9cQ_d6WFwvx2PSVpdJ5MCmo5WFK7XPWaJ60BgtgDXkHlinraelso/s600/soda_5.png"/></a></div>
<p>The important thing to keep in mind is that all your code and data will live in the cloud there is nothing on your own computer. Hope you enjoyed this post and drink up!</p>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-44058509568129669912021-06-25T07:13:00.014-07:002021-06-27T14:26:59.293-07:00Creating Sample Data<p>Sample data plays important roles in testing and benchmarking applications. We need to be mindful of personally identifiable information or PII to not reveal actual user data. SAS does provide procedures such as <a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/statug/statug_surveyselect_syntax01.htm">surveyselect</a> to create extended sample data from existing data but that can be risky if PII is not properly addressed. The use of SAS supplied test data such as SASHELP.CLASS or SASHELP.BASEBALL and the like are just too small to be of any value, especially when benchmarking.<p>
<p>As a result, I have created a macro named sampledata that creates fictious data of any size you like in terms of rows. You can modify the code to your desires to expand the number of columns but this gives you a very good start and explains the use of random numbers when applied to the SAS byte() function.</p>
<p>It is important to understand the American Standard Code for Information Interchange or ASCII character set. As it turns out my very <a href="https://kansascode.blogspot.com/2011/11/display-ascii-codes.html">first blog post</a> from 10 years ago was on the creation of the printable ASCII values - see below (click to see a clearer image).</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlaVPLD7-oLC7LeMajh9lO4EmdWTgNL0Ai9WXxgJSP6lVdX2WvZQyhqenNe_1L1K7etrKB9LvM4HeKJfCirTx-XcX5Q42883q1gYryYxPPMmJXyf3cCC9Cs6qf2IjOajqtbYx3si3Mn90/s533/ascii.png" style="display: block; padding: 1em 0; text-align: center; "><img title = "Click to see sharper image" alt="" border="0" width="320" data-original-height="502" data-original-width="533" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlaVPLD7-oLC7LeMajh9lO4EmdWTgNL0Ai9WXxgJSP6lVdX2WvZQyhqenNe_1L1K7etrKB9LvM4HeKJfCirTx-XcX5Q42883q1gYryYxPPMmJXyf3cCC9Cs6qf2IjOajqtbYx3si3Mn90/s320/ascii.png"/></a></div>
<p>As you can see, numeric values 0 - 9 equate to decimal 48 to 57 in ASCII. If you use the SAS <a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/n1jfxetjdn0nxan1w4e64c7rouz9.htm">byte()</a> function on ASCII 57 as BYTE(57) it will return a 9. Similarly upper case alphabet characters A - Z are represented as ASCII values 65 - 90. Finally, lowercase a - z are the same as ASCII 97 - 122.</p>
<p>Another critical consideration to create the data is to understand that SAS uses numbers to reprsent dates. SAS dates start with zero (0) on January 1, 1960 and increase or decrease from that base value. You will see a fake date of birth (dob) value has been created for each row. I chose a 60 year time span from 1930 - 1990 based on the SAS date methodology - see below. A span of 30 years in SAS is 365.25 * 30 = 10957 rounded. In order to expand this to a 60 year time period, I used the RAND() function and if the return value was less than 0.5 the value was multiplied by a positive 1 otherwise it was multiplied by a negative 1.</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3jZa3qtykxSaer-pF8eK_Vs6jp7S6OT8Yxxcr48fH5FpESGEzvfSd8FYBQ3dBcgRgYI-osAf20qUprwymNt42h06Mj-VSoOKVanAlW2eS3H10snTh5TdLuZhtUTqokRBhbFZtF-r0u30/s0/1930_1990.png" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" data-original-height="69" data-original-width="572" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3jZa3qtykxSaer-pF8eK_Vs6jp7S6OT8Yxxcr48fH5FpESGEzvfSd8FYBQ3dBcgRgYI-osAf20qUprwymNt42h06Mj-VSoOKVanAlW2eS3H10snTh5TdLuZhtUTqokRBhbFZtF-r0u30/s0/1930_1990.png"/></a></div>
<p>The most important piece of this code is the use of the <a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p0fpeei0opypg8n1b06qe4r040lv.htm">RAND()</a> function. <a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p0gw58qo85qp56n1kbpiz50ww8lv.htm">CALL steaminit()</a> is used to specify a starting seed value used by the RAND() function. If a positive number is used in STREAMINIT() the sequence is repeatable. The RAND() function will return a fractional value between 0 and 1 that you can then multiply by whatever value you like. You can see that in the case of both the firstname and lastname values that each are 16 characters in length so I used byte(int(97 + 26 * rand('uniform')) to generate lower case characters (the range A - Z is 26) and lower case ASCII values start at 97. An outer loop was used to create a name that can be up to 16 characters in length. I set a minimum length of 3 so the loop will be used anywhere between 3 and 16 times to create the names.</p>
<p>The distribution of sex ('F' / 'M') is set at 50.8% female. This ratio was taken from the <a href="https://www.census.gov/quickfacts/fact">Census bureau</a>. The SAS <a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proccas/p0uc1bw1rmz53qn1lpf2q6f5hv3i.htm#!">IFC()</a> function came in handy here.</p>
<p>I wanted to add some reality to this fake data and did that by using the SASHELP.ZIPCODE data set and augmented it by adding in an incrementing key value (1 to number of rows). This makes it easy to randomly extract a zip code from the hash object. I used the hash object reference of H along with its <a href="https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/ds2ref/n1na7m2am50i5hn1pvt6u7lmwhmn.htm">num_items</a> attribute as h.num_items to return the total number of rows loaded into the hash object. Once a mathc is made (it has to in this scenario) then the associated satellite values are copied into the program data vector (PDV). The value column can be whatever you like but here it is set to a random value between a low side of 30,000 up to 1,000,000 so it could be the value of a house.</p>
<p>The results of running this code via <b>%sampledata()</b> will generate the output shown below. After the image, you will find the entire source code used to create the output.</p>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg13lwzWJRnPQY1xGFC0oX7IcKs4KGvKckIH0Pr6UWpbdERmLhYpDCYwzRAaSEV8Q_A6tzE-CnGPVr9CHj2evVJjm5UF23kgOkblFw6Cw5kg_MNrwXsf1yudg8mBNNpI_Z_KR0dMjgiUkE/s900/sampledata.png" style="display: block; padding: 1em 0; text-align: center; "><img title = "Click to see sharper image" alt="" border="0" width="600" data-original-height="422" data-original-width="900" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg13lwzWJRnPQY1xGFC0oX7IcKs4KGvKckIH0Pr6UWpbdERmLhYpDCYwzRAaSEV8Q_A6tzE-CnGPVr9CHj2evVJjm5UF23kgOkblFw6Cw5kg_MNrwXsf1yudg8mBNNpI_Z_KR0dMjgiUkE/s600/sampledata.png"/></a></div>
<pre>
%macro sampledata(
rows = 1000000
, outdsn = work.sampledata
, seed = 123
);
/* dsn of zip codes with incrementing key */
data work.zipcodes;
key + 1;
set sashelp.zipcode(keep = zip city statecode areacode);
run;
data &outdsn(keep = id -- value);
attrib
id length = 8
firstname length = $16
lastname length = $16
sex length = $1
city length = $35
statecode length = $2
zip length = 8
phone length = $10
dob length = 4 format = mmddyy10.
age length = 3
value length = 8 format = dollar12.
areacode length = 8
;
dcl hash h(dataset: "work.zipcodes");
h.definekey('key');
h.definedata('zip', 'city', 'statecode', 'areacode');
h.definedone();
/* seed for random function */
call streaminit(&seed);
do id = 1 to &rows;
/* firstname can be between 3 and 16 characters long */
t_firstlen = max(3, ceil(16 * rand('uniform')));
do t_i = 1 to t_firstlen;
if t_i = 1 then firstname = byte(int(65 + 26 * rand('uniform')));
else substr(firstname, t_i, 1) = byte(int(97 + 26 * rand('uniform')));
end;
t_lastlen = max(3, ceil(16 * rand('uniform')));
do t_i = 1 to t_lastlen;
if t_i = 1 then lastname = byte(int(65 + 26 * rand('uniform')));
else substr(lastname, t_i, 1) = byte(int(97 + 26 * rand('uniform')));
end;
/* Females 50.8% per census: https://census.gov/quickfacts */
sex = ifc(rand('uniform') >= 0.508, 'F', 'M');
key = ceil(h.num_items * rand('uniform'));
if h.find() = 0 then do;
phone = put(areacode, 3.);
do t_i = 4 to 10;
substr(phone, t_i, 1) = byte(int(48 + 10 * rand('uniform')));
end;
end;
/* (365.25 * 30) = 10957 or 30 years of days */
dob = ceil(10957 * rand('uniform')) * ifn(rand('uniform') < .5, 1, -1);
age = int(yrdif(dob, date(), 'actual'));
value = max(1000000 * rand('uniform'), 30000);
output;
end;
stop; /* terminates this data step */
run;
%mend;
</pre>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7954851561018760025.post-29621801712805470852021-06-03T08:30:00.003-07:002021-06-03T13:21:29.165-07:00Recursive Query with PROC SQL<p>The SAS implementation of SQL is not as robust as most relational databases. One example where SAS falls short is in the area of recursive queries. I know that SQL Server and PostgreSQL handle recursive queries via the use of <a href="https://learnsql.com/blog/when-to-use-cte/">common table expressions or CTEs</a> using the WITH statement. Hopefully SAS will add this feature in a future release since CTEs were included in the <a href="https://learnsql.com/blog/history-of-sql-standards/">SQL:1999</a> standard.</p>
<p>In the mean time, I have emulated an existing article "<a href="https://www.postgresqltutorial.com/postgresql-recursive-query/">Learn PostgreSQL Recursive Query By Example</a>". Please reference that article for additional details and to compare the different techniques. Credit must be given to my co-worker and excellent SAS programmer, <a href="https://www.linkedin.com/in/davedevoll/">Dave Devoll</a> for the concept he used recently.</p>
<pre>
data employees;
infile datalines dsd;
input
employee_id : 4.
full_name : $32.
manager_id : 4.
;
datalines;
1, 'Michael North', .
2, 'Megan Berry', 1
3, 'Sarah Berry', 1
4, 'Zoe Black', 1
5, 'Tim James', 1
6, 'Bella Tucker', 2
7, 'Ryan Metcalfe', 2
8, 'Max Mills', 2
9, 'Benjamin Glover', 2
10, 'Carolyn Henderson', 3
11, 'Nicola Kelly', 3
12, 'Alexandra Climo', 3
13, 'Dominic King', 3
14, 'Leonard Gray', 4
15, 'Eric Rampling', 4
16, 'Piers Paige', 7
17, 'Ryan Henderson', 7
18, 'Frank Tucker', 8
19, 'Nathan Ferguson', 8
20, 'Kevin Rampling', 8
;
run;
</pre>
<p>The very short macro function code for %isblank can be <a href="https://kansascode.blogspot.com/2015/05/four-short-macro-functions.html">found here</a>. The initial query in the below code only returns a single row for employee_id = 2, Megan Berry. After that, a test is made to ensure that at least one row was returned. If not, an error message will be generated and the macro will terminate.</p>
<p>If all is good, the output that was written to the lev0 data set is copied to a data set named results. This is done so it can be used to append subsequent query results. The next SQL statement uses a sub-query to reference back to the prior data set using %eval(&iter -1). If that query returned rows (&sqlobs > 0) then it is append to the results data set and is repeated until no rows are returned.</p>
<pre>
%macro recursive(id =, iter = );
%if %isblank(&id) %then %do;
%put %str(E)RROR: Must pass in a PK value.;
%return;
%end;
%if %isblank(&iter) %then %do;
proc sql;
create table lev0 as
select employee_id
, full_name
, manager_id
from employees
where employee_id = &id
;
quit;
%if &sqlobs = 0 %then %do;
%put %str(E)RROR: No entries found using ID = &id;
%return;
%end;
%if %sysfunc(exist(results)) %then %do;
/* Clear out old version should it exist */
proc delete data = results;
run;
%end;
data results;
set lev0;
run;
%let iter = 1;
%end;
proc sql;
create table lev&iter as
select employee_id
, full_name
, manager_id
from employees
where manager_id in(
select distinct employee_id
from lev%eval(&iter - 1))
;
quit;
%if &sqlobs %then %do;
proc append
base = results
data = lev&iter
;
run;
%recursive(id = &id, iter = %eval(&iter + 1 ));
%end;
%mend;
%recursive(id = 2);
</pre>
<div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHp2e7HTDDwC1EKPxK_FuSW8__EkYr57Fvb2cyvnmAjAplB9eeGXK5L5Xm8dhz30I6NSF-5v1FSF58vEAwmk1c6qJcsDocq0Q3-E9mo7s0fjZMH9p3voQp1clt_SjX9plwFOzMhTUPjko/s0/recursive.png" style="display: block; padding: 1em 0; text-align: center; "><img alt="" border="0" data-original-height="225" data-original-width="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHp2e7HTDDwC1EKPxK_FuSW8__EkYr57Fvb2cyvnmAjAplB9eeGXK5L5Xm8dhz30I6NSF-5v1FSF58vEAwmk1c6qJcsDocq0Q3-E9mo7s0fjZMH9p3voQp1clt_SjX9plwFOzMhTUPjko/s0/recursive.png"/></a></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-44064908322845105482020-12-07T14:44:00.001-08:002020-12-11T07:40:49.271-08:00DOW Sort<p><span style="font-family: arial;">Sorting data in ascending or descending order is a very easy and straightforward process. However, there are times when a simple sort does not suffice. Consider a scenario of submitting a large number of processes in a SAS grid environment that has a limit on the number of active parallel processes.</span></p><p><span style="font-family: arial;">In that scenario, you will want to mix tasks that take a long time with those quicker running tasks. There is normally a key driver that can be used to identify longer running processes. The number of rows and columns are prime candidates to sort the data.</span></p><p><span style="font-family: arial;">This example uses the available SASHELP.CLASS data set and sorts it by age and height in ascending order. The ability to read SAS data sets in random order via the <a href="https://documentation.sas.com/?cdcId=vdmmlcdc&cdcVersion=8.1&docsetId=lestmtsref&docsetTarget=p00hxg3x8lwivcn1f0e9axziw57y.htm&locale=en">POINT=</a> option in a SET statement make this relatively easy. The idea is to read the highest or last row with the first X rows that are lower in value. The technique used is a variant of the <a href="https://support.sas.com/resources/papers/proceedings09/038-2009.pdf">DOW Loop</a> created and popularized by SAS Gurus Don Henderson, Paul Dorfman and Ian Whitlock.</span></p><p><span style="font-family: arial;">The below data set is sorted by age and height.</span></p><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5xRuXS91jTfrGpRfhuFyh90SJrPXRX7qdLt-ma8ENDIDJUzE4pI-Ez2bcVio2hSaPKNtYLO-Mj0GBKOKKEdjMLtKk3d77H060PACFfvF9W-DIDwmvPNtxpsJzBBdJECNiYYQd87TeXBE/s496/class1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="412" data-original-width="496" height="333" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5xRuXS91jTfrGpRfhuFyh90SJrPXRX7qdLt-ma8ENDIDJUzE4pI-Ez2bcVio2hSaPKNtYLO-Mj0GBKOKKEdjMLtKk3d77H060PACFfvF9W-DIDwmvPNtxpsJzBBdJECNiYYQd87TeXBE/w400-h333/class1.png" width="400" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: arial;">The following data set shows the result of reading the last row then the top 5 rows. After that the second to the last row is output followed by rows 6 through 10</span></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjK86UwoJQKTahEUqS6m_EZ0VMw4hzKD25j6noxGe1RNGIJI2OyMjbd_1wecY4RegC9QueuYYAMhNIMsZcDGU3mKB96XWW2eo7OG_YEgNR7T1jJKUIbfie-3Ji-RdB7IELDwB8pNYAjT9o/s497/class2.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="406" data-original-width="497" height="326" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjK86UwoJQKTahEUqS6m_EZ0VMw4hzKD25j6noxGe1RNGIJI2OyMjbd_1wecY4RegC9QueuYYAMhNIMsZcDGU3mKB96XWW2eo7OG_YEgNR7T1jJKUIbfie-3Ji-RdB7IELDwB8pNYAjT9o/w400-h326/class2.png" width="400" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"><br /></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;">/*****************************<wbr></wbr>******************************<wbr></wbr>********************<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"> Program: sort_dow.sas<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"> Author: Tom Bellmer<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="color: green; font-family: courier;"> Created: 12/07/2020 @ 1:25:37 PM</span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;">SAS Version: SAS 9.4 (TS1M6)<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"> OS: LIN X64<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"> Purpose: Places subrows under the highest value via a DOW loop<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"> Notes: Assumes the input data set is sorted in ascending order<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"> will be used to stuff low value rows below high for RSUBMITS<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"> to reduce expected time in each parallel session<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="color: green; font-family: courier;"> </span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"> Modifications in descending order<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;">FL-YYYYMMDD <wbr></wbr> Description<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;">----------- ------------------------------<wbr></wbr>------------------------------<wbr></wbr>--------<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;"> 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;"><span style="font-family: courier;">....5....0....5....0....5....<wbr></wbr>0....5....0....5....0....5....<wbr></wbr>0....5....0....5....0<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;">******************************<wbr></wbr>******************************<wbr></wbr>*******************/</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: navy;">%macro</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> sort_dow(<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> dsnin =<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> , subrows = </span><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal;">5</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> , dsnout = sort_dow<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;">);<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">%if</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> not </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">%sysfunc</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;">(exist(&dsnin)) </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">%then</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">%do</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;">;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">%put</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">%str</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;">(E)RROR: Invalid input data set: &dsnin..;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">%return</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;">;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">%end</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;">;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> data &dsnout;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> if </span><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal;">0</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> then set &dsnin nobs = totobs;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> do until(totobs = i);<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> set &dsnin point = totobs;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> totobs = totobs - </span><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal;">1</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;">;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> output;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> if totobs = i then stop;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> do _n_ = </span><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal;">1</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> to &subrows;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> i + </span><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal;">1</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;">;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> set &dsnin point = i;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> output;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> if totobs = i then stop;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> end;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> end;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> stop;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> run;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: navy;">%mend</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;">;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green;">/*EOF: sort_dow.sas */</span><span style="font-size: 10pt;"><u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-size: 10pt;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-size: 10pt;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-size: 10pt;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: navy;">proc</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> </span><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: navy;">sort</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">data</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> = sashelp.class </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">out</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> = class;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> </span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue;">by</span><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> age height;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: navy;">run</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;">;<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"><u></u> <u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;">%<b><i>sort_dow</i></b>(<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> dsnin = class<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="font-family: courier;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"> , subrows = </span><b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal;">5</span></b><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;"> , dsnout = sort_dow<u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-size: small; margin: 0px;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black;"><span style="font-family: courier;">);</span></span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-24282873380386939992020-10-26T05:58:00.000-07:002020-10-26T05:58:19.395-07:00FREQ N Faster<p> </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPJ_vepqcwOXxXLfA0MFi65xPKNSKiU-H16Fvu8QppdtomHFzQqkIOcuEL6GLjw4SWcAI5DvXLgy4poizDDqQIn5Lt2yMGR0cQGBhD8UFohBIIQKvQefb_i8lxdFGPvi92_-ipvzkgxiM/s1080/speedy.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="809" data-original-width="1080" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPJ_vepqcwOXxXLfA0MFi65xPKNSKiU-H16Fvu8QppdtomHFzQqkIOcuEL6GLjw4SWcAI5DvXLgy4poizDDqQIn5Lt2yMGR0cQGBhD8UFohBIIQKvQefb_i8lxdFGPvi92_-ipvzkgxiM/w400-h300/speedy.jpg" width="400" /></a></div><br /><p></p><p>I needed to capture and count the unique value of each character column in SAS data sets. My first thought was to use PROC FREQ and pass all the character variables to the TABLE statement.</p><p>Unfortunately, I quickly ran into an out of memory issue and was unable to specify a <a href="https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=hostunx&docsetTarget=n09y5anvvpzrmnn0ztkyf59qgzvr.htm&locale=en">memsize</a> setting due to established constraints. It is a shame to have to call PROC FREQ once for each variable but that approach did get me past the out of memory issue.</p><p>The best way to test different approaches was to create some test data. That is the purpose of the createsampledata macro - source code can be found below. That code created random upper case characters between ASCII values 65 and 90 (A - Z). In order to simulate real world data better, the length of each value can fluctuate from 0 to 16 characters. Instead of making each value unique, proc append is used to copy in existing rows to again better simulate real world data.</p><p>I decided to try a hybrid approach to push some, but not all variables to the TABLE statements. The below code shows the results using the sample test data. Your mileage may vary based on the number of rows processed, character length and the cardinality of the data.</p><p>As you can see in the below chart, the sweet spot for this set of data is to use 10 columns in the table statement. After that the elapsed time increases. Try utilizing this technique to reduce the amount of time it takes to process your freqn data.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtgv-t-emRufj3pqTofUW_YQ_lojI_jp-KwF0hPpW_vjlxWTz2bY2JWI9VsNjs3s4fGMaQwuO9W3KI8WRce180-_kcHCx7Uuy3LuHQdNmtzr2c5oJwtoGzoVVEwFrGcdvLQEcUSOzbqU4/s640/freqnfast+%25281%2529.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="480" data-original-width="640" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtgv-t-emRufj3pqTofUW_YQ_lojI_jp-KwF0hPpW_vjlxWTz2bY2JWI9VsNjs3s4fGMaQwuO9W3KI8WRce180-_kcHCx7Uuy3LuHQdNmtzr2c5oJwtoGzoVVEwFrGcdvLQEcUSOzbqU4/w640-h480/freqnfast+%25281%2529.png" width="640" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><br /></div><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%macro createsampledata(<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> rows = 1000000<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> , byvalue = 1000<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> %local i;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> proc datasets<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> lib = work<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> nolist<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> kill<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> ;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> quit;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> data results;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> attrib<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> stepvalue length = 3<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> duration length = 8 format = time12.3<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> ;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> call missing(of _all_);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> stop;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> run;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> <u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> /* Create sample data */<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> data tempdata(drop = t_:);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> array dummy {*} $16 char_1 - char_100;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> call streaminit(123); <u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> <u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> do t_0 = 1 to &rows by &byvalue;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> do t_1 = 1 to dim(dummy);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> do t_2 = 1 to int(17 * rand('uniform'));<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> substr(dummy[t_1], t_2) = byte(int(65 + 26 * rand('uniform')));<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> end;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> end;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> output;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> end;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> run; <u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> <u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> /* Not everything is unique so add via byvalue */<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> %do i = 1 %to &byvalue;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> proc append<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> base = sampledata<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> data = tempdata<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> ;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> run;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> %end;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%mend;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%macro freqnfaster(stepvalue = 1);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> %local i j starttime columncount;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> <u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> proc sql noprint;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> select name<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> into :name1 -<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> from dictionary.columns<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> where libname = "WORK"<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> and memname = "SAMPLEDATA"<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> and type = 'char'<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> ;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> quit;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> %let columncount = &sqlobs; <u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> </span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> %let starttime = %sysfunc(datetime());<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> %do i = 1 %to &columncount %by &stepvalue;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> proc freq<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> data = work.sampledata<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> order = freq<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> noprint<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> ;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> %do j = &i %to %sysfunc(min(%eval(&i + &stepvalue -1), &columncount));<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> table &&name&j / missing out = work.freqout&j;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> %end;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> run;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> %end;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> <u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> proc sql;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> insert into work.results<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> set<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> stepvalue = &stepvalue<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> , duration = (datetime() - &starttime)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> ;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> quit;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%mend;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%createsampledata(<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> rows = 1000000<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> , byvalue = 1000<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 1)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 2)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 3)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 5)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 8)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 10)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 15)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 20)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 25)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 30)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 50)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">%freqnfaster(stepvalue = 100)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">ods _all_ close;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">ods listing gpath = "/myfolder";<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">ods graphics / reset = index imagename = 'freqnfast';<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">title 'PROC FREQ Results of 1M rows and 100 columns';<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">proc sgplot data = results;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> hbar stepvalue /<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> response = duration<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> stat = mean<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> datalabel<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> ;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> xaxis grid label = 'Duration';<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;"> yaxis grid label = 'Step Value';<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: small; margin: 0px;"><span style="font-family: "Courier New"; font-size: 10pt;">run;</span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-47490918245107473442020-08-11T08:58:00.006-07:002020-08-24T08:49:23.797-07:00find text in a file<p> </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgs2R6VbOqs74FC4LuveYuhc_cU36EAj29E4kBkp1ybyLooH5hgxaMr98dKFI6sTDiH0Usajgq1CCx42gyRI3-Qi-EA4kapGDEdgqjQ1pjv8fiQbi_hucNmEDM3T_Q95PEraOMKUolS3Go/s603/binoc.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="411" data-original-width="603" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgs2R6VbOqs74FC4LuveYuhc_cU36EAj29E4kBkp1ybyLooH5hgxaMr98dKFI6sTDiH0Usajgq1CCx42gyRI3-Qi-EA4kapGDEdgqjQ1pjv8fiQbi_hucNmEDM3T_Q95PEraOMKUolS3Go/s0/binoc.jpg" /></a></div><p>Over time we tend to collect a large number of program files. It never fails that after much time has past that you need to access some code in a file but can't recall which program the keyword was in.</p><p>I work on a Linux operating system so I can use the <a href="https://man7.org/linux/man-pages/man1/grep.1.html">GREP </a>command to handle the task but I never remember the syntax options and do not like the way the data is returned. That is why I wrote a macro named %findtext() that handles this for you in SAS via the use of a <a href="https://documentation.sas.com/?docsetId=hostunx&docsetTarget=n1ceb0xedanuj3n19l3g73awk1wf.htm&docsetVersion=9.4&locale=en">PIPE </a>command.</p><p>If want to work for the keyword 'bitwise' in all SAS programs in a folder and any sub-folders that GREP command will be as follows:</p><p><b style="background-color: white; color: #222222; font-family: verdana, sans-serif; font-size: 10pt;">> grep -nRi 'updatestagingdetails' '/em_data1/prod/macro' --include=*.sas</b></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="font-family: verdana, sans-serif; font-size: 10pt;">/em_data1/prod/macro/etl.sas:<wbr></wbr>524: %updatestagingdetails(env_out=<wbr></wbr>&env_out.);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="font-family: verdana, sans-serif; font-size: 10pt;">/em_data1/prod/macro/<wbr></wbr>updatestagingdetails.sas:2:* <wbr></wbr> Program: updatestagingdetails.sas<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="font-family: verdana, sans-serif; font-size: 10pt;">/em_data1/prod/macro/<wbr></wbr>updatestagingdetails.sas:9:* <wbr></wbr> Usage: %updatestagingdetails(dsn=<wbr></wbr>work.stagingdetails,env_out=%<wbr></wbr>env())<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="font-family: verdana, sans-serif; font-size: 10pt;">/em_data1/prod/macro/<wbr></wbr>updatestagingdetails.sas:21:%<wbr></wbr>macro updatestagingdetails( dsn = work.stagingdetails, env_out = %env() ) ;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="font-family: verdana, sans-serif; font-size: 10pt;">/em_data1/prod/macro/<wbr></wbr>updatestagingdetails.sas:40:/*<wbr></wbr>EOF: updatestagingdetails.sas */</span></p><p>Notice in the above output that the values are separated by a colon (:) and it is overall hard to read in my opinion. This is where the SAS SCAN and FIND functions come into play to create a cleaner easier to read result.</p><p>The SAS macro call will be as follows (only the first two parameters are required):</p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><span style="font-size: x-large;"><b>%findtext</b><b>(</b></span><span style="font-size: small;"><u></u><u></u></span></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new"; font-size: 14pt;"> path = /em_data1/prod/macro<u></u><u></u></span></b></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new"; font-size: 14pt;"> , text = updatestagingdetails <u></u><u></u></span></b></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><i><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , ignorecase = Y<u></u><u></u></span></i></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><i><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , exactmatch = N<u></u><u></u></span></i></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><i><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , extension = sas<u></u><u></u></span></i></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><i><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , outdsn = findtextresults<u></u><u></u></span></i></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><i><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , printresults = Y<u></u><u></u></span></i></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">);</span></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgnbE8YVMc0JN7rk64gPNsnEWX8yjnPxsLjmWVuLXTgo6-9ZzbzI3euRGCUWrjWIeC9k2Mlqcvqlwzq9AMDieQdVamesjWzMZsig0AN8qbVq8-zTnmFRLtHW_bqEabUFSSmqMDhyphenhyphenzYXcww/s888/rpt.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="201" data-original-width="888" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgnbE8YVMc0JN7rk64gPNsnEWX8yjnPxsLjmWVuLXTgo6-9ZzbzI3euRGCUWrjWIeC9k2Mlqcvqlwzq9AMDieQdVamesjWzMZsig0AN8qbVq8-zTnmFRLtHW_bqEabUFSSmqMDhyphenhyphenzYXcww/s640/rpt.jpg" width="640" /></a></div><p><br /></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";">/*****************************<wbr></wbr>******************************<wbr></wbr>********************<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> Program: findtext.sas<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> Author: Tom Bellmer<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";">Responsible: Tom Bellmer<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> Created: 08/10/2020 @ 3:00:45 PM<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";">SAS Version: SAS 9.4 (TS1M6)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> OS: LIN X64<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> Purpose: read the contents of files searching for the string value<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> Usage: %findtext(path = /folder, string = sql);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> Notes: colon is used as output separator. Scan() function had issues<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> with third column that contained colons.<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> Parameters: path = (folder path to be searched)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> text = (text to be searched inside each file)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> ignorecase = Y/N to ignore the case of the &string.<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> exactmatch = N/Y to match whole &string value<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> extension = sas (file extension, use * for all files)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> outdsn = findtextresults (output data set name)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> printresults = Y (proc print the results? Y/N)<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> Modifications in descending order<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";">FL-YYYYMMDD <wbr></wbr> Description<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";">----------- ------------------------------<wbr></wbr>------------------------------<wbr></wbr>--------<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";">....5....0....5....0....5....<wbr></wbr>0....5....0....5....0....5....<wbr></wbr>0....5....0....5....0<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";">******************************<wbr></wbr>******************************<wbr></wbr>*******************/</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: navy; font-family: "courier new";">%macro</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> findtext(<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> path =<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , text =<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , ignorecase = Y<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , exactmatch = N<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , extension = sas<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , outdsn = findtextresults<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , printresults = Y<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%local</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> options i;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%do</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> i = </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">1</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%to</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">100</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%local</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> pathname&i;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%end</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%if</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> %<b><i>isblank</i></b>(&path) %<b><i>then</i></b> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%do</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%put</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%str</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">(E)RROR: Must pass in a value for path.;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%return</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%end</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%if</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> %<b><i>isblank</i></b>(&text) %<b><i>then</i></b> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%do</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%put</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%str</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">(E)RROR: Must pass in a value for text.;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%return</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%end</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";">/* i = ignores case, n = show matched line number<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";"> , R = recursively, w = match whole word */</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%let</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> options = -nR;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%if</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%upcase</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">(&ignorecase) = Y </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%then</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%let</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> options = &options.i;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%if</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%upcase</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">(&exactmatch) = Y </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%then</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%let</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> options = &options.w;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> filename search pipe </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">"grep &options '&text' '&path' --include='*.&extension'"</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> data &outdsn(drop = fullfilename findpos);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> attrib<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> Path length = $</span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">256</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> label = </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">'Path'</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> Filename length = $</span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">128</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> label = </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">'Filename'</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> Lineno length = </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">8</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> label = </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">'Line No'</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> Code length = $</span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">256</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> label = </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">'Code'</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> Fullfilename length = $</span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">256</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> label = </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">'Full Filename'</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> ;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> infile search;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> input;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> fullfilename = scan(_infile_, </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">1</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">, </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">':'</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> filename = scan(fullfilename, -</span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">1</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">, </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">"/"</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> path = substr(_infile_, </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">1</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">, lengthn(fullfilename) - lengthn(filename));<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> lineno = input(scan(_infile_, </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">2</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">, </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">':'</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">), </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">8.</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> findpos = find(_infile_, </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">":"</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> findpos = find(_infile_, </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">":"</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">, findpos + </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">1</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> code = substr(_infile_, findpos + </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">1</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">);<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> run;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> filename search clear;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%if</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> %<b><i>getattr</i></b>(dsn = &outdsn, attr = nlobs) = </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">0</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%then</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%do</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> proc sql;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> insert into &outdsn<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> set path = </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">"&path"</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , filename = </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">"N/A"</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , lineno = </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">0</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> , code = </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">"No results were found using '&text'"</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> ;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> quit;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%end;</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%if</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%upcase</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">(</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%substr</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">(&printresults, </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">1</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">, </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">1</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">)) = Y </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%then</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%do</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> proc sql noprint;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> select distinct path<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> into :pathname1 -<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> from &outdsn;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> quit;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> <u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> title </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">"Search results for: &text"</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%if</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> &sqlobs = </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">1</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%then</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%do</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> title2 </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: purple; font-family: "courier new";">"In Path: &pathname1"</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%end</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> proc print data = &outdsn;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%if</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> &sqlobs = </span><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: teal; font-family: "courier new";">1</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%then</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%do</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> var filename lineno code;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%end</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> run;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> title;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"> </span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: blue; font-family: "courier new";">%end</span><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: navy; font-family: "courier new";">%mend</span></b><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";">;<u></u><u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: black; font-family: "courier new";"><u></u> <u></u></span></p><p class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, helvetica, sans-serif; font-size: small; margin: 0px;"><span face="" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: green; font-family: "courier new";">/*EOF: findtext.sas */</span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-1084801047156561932020-05-21T12:28:00.000-07:002020-05-21T14:18:35.917-07:00Future Proofing<html>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh52kl1midVcB-dDiom7P-Eh5JrXoDNKEnAx6Xdu1e0WgOWrpf_9kPmxN4Rnz1ZS2ic4LE-OeVpbR6xDavdH2ysl-EsebXsqP9tJtWBAIiconFx7eaPX2BC5gxT4LnV734jd2pSQCQjQGQ/s1600/time-lapse-photography-of-vehicle-tail-lights-3341307+%25281%2529+%25281%2529.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh52kl1midVcB-dDiom7P-Eh5JrXoDNKEnAx6Xdu1e0WgOWrpf_9kPmxN4Rnz1ZS2ic4LE-OeVpbR6xDavdH2ysl-EsebXsqP9tJtWBAIiconFx7eaPX2BC5gxT4LnV734jd2pSQCQjQGQ/s1600/time-lapse-photography-of-vehicle-tail-lights-3341307+%25281%2529+%25281%2529.jpg" data-original-width="640" data-original-height="426" /></a></div>
<p>It does not happen often, but it does happen. An event such as a server upgrade requires related changes to settings used by programs. Of course, alterations are not merely related to equipment changes but things like name or address changes do happen over time. This blog post reveals one technique to data drive those inevitable changes.</p>
<p>The approach used here was influenced by an equipment upgrade as well as SAS software upgrade from version 9.4m3 to 9.4m6. Instead of writing conditional macro code such as %IF-%THEN/%ELSE, the below code uses a macro function to return the name/value pair result directly inline. This technique is not only much more compact and flexible but it also retains changes over time.</p>
<p>The first thing needed is a permanent SAS data set to retain the values. Below is the structure that I used to support changes by SAS version that has been truncated to the first 9 characters so that incremental hot fixes do not impact the program. Slowly changing dimensions (SCD) are used via the effective begin and end dates to retain the history. The actual code is shown below.</p>
<pre>
data mylib.lookup;
infile datalines dsd dlm = '~';
attrib
pk length = 5
version length = $16
eff_begin_date length = 4 format = date9. informat = date9.
eff_end_date length = 4 format = date9. informat = date9.
name length = $128
value length = $256
createdby length = $8
createdate length = 4 format = date9. informat = date9.
;
input (pk -- createdate) (:);
datalines;
1~9.04.01M3~01jan2017~30dec9999~WEB~oldserver@myco.org:8343~myid~21may2020
2~9.04.01M6~01may2020~30dec9999~WEB~newserver@myco.org:8343~myid~21may2020
3~9.04.01M3~01jan2017~30dec9999~METAPORT~8562~myid~21may2020
4~9.04.01M6~01may2020~30dec9999~METAPORT~8561~myid~21may2020
run;
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj26QygIt5RyOuIhikLEzuZgyHJbhMEnYpjmnoEi_3BKhA7_myRpIO7132SRleI9Gt26tYyKn8K1oT3juMzeXJUFp7thRrNk1rknsnMqtnROx4ijS2La2KuQsOZSB2wECnqFSFc9GNrf18/s1600/lookup.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj26QygIt5RyOuIhikLEzuZgyHJbhMEnYpjmnoEi_3BKhA7_myRpIO7132SRleI9Gt26tYyKn8K1oT3juMzeXJUFp7thRrNk1rknsnMqtnROx4ijS2La2KuQsOZSB2wECnqFSFc9GNrf18/s640/lookup.png" width="640" height="88" data-original-width="1600" data-original-height="219" /></a></div>
/*******************************************************************************
Program: lookup.sas
Author: Tom Bellmer
Created: 05/21/2020 @ 1:46:19 PM
SAS Version: SAS 9.4 (TS1M3)
OS: LIN X64
Purpose: Macro function to return value from a lookup name. Uses slowly
changing dimension to support changes within a version
Usage: %let port = %lookup(metaport);
Notes: version is truncated to first nine characters to avoid hotfix
changes
Modifications in descending order
FL-YYYYMMDD Description
----------- --------------------------------------------------------------------
1 1 2 2 3 3 4 4 5 5 6 6 7 7 8
....5....0....5....0....5....0....5....0....5....0....5....0....5....0....5....0
*******************************************************************************/
%macro lookup(name);
%local dsid rc retval;
%let dsid = %sysfunc(
open(
mylib.lookup(
where = (
"%substr(&sysvlong, 1, 9)" = version
and %sysfunc(date()) >= eff_begin_date
and %sysfunc(date()) <= eff_end_date
and "%upcase(&name)" = upcase(name)
)
)
)
);
%if &dsid. = 0 %then %put %sysfunc(sysmsg());
%else %do;
%if %sysfunc(fetch(&dsid)) = 0 %then
%let retval = %sysfunc(getvarc(&dsid, %sysfunc(varnum(&dsid., value))));
%else %put %str(E)RROR: no entry found for name = &name..;
%let dsid = %sysfunc(close(&dsid));
%end;
&retval.
%mend;
/*EOF: lookup.sas */
</pre>
</html>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-31131753406658434892020-04-25T18:15:00.000-07:002020-05-22T07:35:17.235-07:00Lock Release<html>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3i2cHBb9T_4GXL6T6pskHEIRB1pHtnFYnTiyDforMLUWFAom8kwa7gbsYZNWHIPU-2wtBv37j61EKE7Ll35hx8D_f_Z1P0igULX8PDO98gF5_5MibDzTASFmkKTkmC3EzM_59PdJD3KA/s1600/lock.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3i2cHBb9T_4GXL6T6pskHEIRB1pHtnFYnTiyDforMLUWFAom8kwa7gbsYZNWHIPU-2wtBv37j61EKE7Ll35hx8D_f_Z1P0igULX8PDO98gF5_5MibDzTASFmkKTkmC3EzM_59PdJD3KA/s1600/lock.jpg" data-original-width="640" data-original-height="480" /></a></div>
<p>Sometimes a SAS data set can get locked such that you can read it but no longer update it. Of course, it is best to attempt to find out who is holding the lock and on simple systems you can use things like the UNIX <a href="https://www.ibm.com/support/knowledgecenter/SSLTBW_2.2.0/com.ibm.zos.v2r2.bpxa500/fuser.htm">fuser </a>command to find the process id and then kill that process. However, things are not that easy on a SAS grid environment with many possible servers.</p>
<p>One way to free up the file is to move the data set (and its index file) to another folder then move it back to release the pointer. The below macro makes it very easy to accomplish those actions. The only required argument is the data set name (dsn). The libref is used to obtain the path via the <a href="https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=p0sycvpqwxea06n1klcseze1mdak.htm&docsetVersion=9.4&locale=en">pathname </a>function. A unique sub-folder is created by using the <a href="https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n0y09yghr5r11cn1b4ocojwqvrqe.htm&docsetVersion=9.4&locale=en">UUIDGEN</a>() function.<p>
<p>The advantage of this technique versus an X command is that it does not rely on the XCMD setting and provides a return code to verify things behaved as expected. You can read thru the below code to see how this is all handled. The %getattr() macro is simply a wrapper of the <a href="https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=p12uchp7hm5h2zn1om2ut816af7h.htm&docsetVersion=9.4&locale=en">ATTRN</a>() function. The source code for the %movefile(), %copyfile() and deletefile() macros can be <a href="https://kansascodesource.blogspot.com/2020/04/move-copy-delete-file-macros.html">found here</a>.
<pre>
/*******************************************************************************
Program: lockrelease.sas
Author: Tom Bellmer
Created: 04/25/2020 @ 8:17:58 PM
SAS Version: SAS 9.4 (TS1M3)
OS: LIN X64
Purpose: Moves data set (and index) to sub-folder then back. This is
needed as data sets get locked and this releases it
Usage: %let x = %lockrelease(dsn = mylib.mymem);
Notes: Better to see if anyone has file locked but if no other option
then use this. It works by changing the file pointer.
Modifications in descending order
FL-YYYYMMDD Description
----------- --------------------------------------------------------------------
1 1 2 2 3 3 4 4 5 5 6 6 7 7 8
....5....0....5....0....5....0....5....0....5....0....5....0....5....0....5....0
*******************************************************************************/
%macro lockrelease(dsn = );
%local
libref
path
memname
guid
starttime
temppath
indexed
rc
;
%let starttime = %sysfunc(datetime());
%if not %sysfunc(exist(&dsn)) %then %do;
%put %str(E)RROR: The dsn: &dsn does not exist;
%return;
%end;
%if not %index(&dsn, .) %then %do;
%put %str(E)RROR: This only supports two level (permanent) data sets;
%return;
%end;
%let libref = %scan(&dsn, 1, .);
%let memname = %scan(&dsn, 2, .);
%let guid = %sysfunc(uuidgen());
%let path = %sysfunc(pathname(&libref));
%let temppath = %sysfunc(dcreate(&guid, &path));
%if %isblank(&temppath) %then %do;
%put %str(E)RROR: Unable to create sub-folder.;
%return;
%end;
%else %do;
%let indexed = %getattr(dsn = &dsn, attr = isindex);
/******** Move dataset (and index) to sub-folder *********************/
%let rc = %movefile(infile = &path/&memname..sas7bdat
, outfile = &temppath/&memname..sas7bdat);
%if &indexed and &rc %then %do;
%let rc = %movefile(infile = &path/&memname..sas7bndx
, outfile = &temppath/&memname..sas7bndx);
%end;
/***** Move dataset (and index) from sub-folder back to original *****/
%if &rc %then %do;
%let rc = %movefile(infile = &temppath/&memname..sas7bdat
, outfile = &path/&memname..sas7bdat);
%if &indexed and &rc %then %do;
%let rc = %movefile(infile = &temppath/&memname..sas7bndx
, outfile = &path/&memname..sas7bndx);
%end;
%end;
%if &rc %then %let rc = %deletefile(&temppath);
%end;
%put %str(N)OTE Elapsed Time: %left(%sysfunc(putn(%sysevalf(%sysfunc(
datetime()) - &starttime), mmss12.3)));
&rc
%mend;
/*EOF: lockrelease.sas */
</pre>
</html>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-8905422722407158892020-01-08T18:46:00.000-08:002020-01-09T06:56:15.960-08:00Importing a delimited file<html>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeVQ1g7DwWA_gX-v6lSUKBpHXvXAQ9KE5Vanq3roxkahYc-InBl8bo4JGwdaVB9mfmvz_osgR-H_Ztaqp1DaRYDSQnicqCcEGB0BS04abvmwDxpe5P9A105RvZV4hqmu-KtzWiqYdaGIU/s1600/100.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeVQ1g7DwWA_gX-v6lSUKBpHXvXAQ9KE5Vanq3roxkahYc-InBl8bo4JGwdaVB9mfmvz_osgR-H_Ztaqp1DaRYDSQnicqCcEGB0BS04abvmwDxpe5P9A105RvZV4hqmu-KtzWiqYdaGIU/s200/100.gif" width="180" height="200" data-original-width="220" data-original-height="244" /></a></div>
<p>As it turns out, this is my 100th blog post since I started doing this back in November 2011. I create these posts as a service to other SAS users that might find some value in things that I have encountered as an issue and found a solution or just pointing out another way to do things.</p>
<p>This post is about a very clever and efficient way to import a delimited file. Credit for this idea goes out to my sagacious co-worker, Dave Devoll.</p>
<p>Let's assume you have a file of comma separated values (CSV) that should be imported into a data set with the same structure as the sashelp.class data set. The use of the <b>IF 0 THEN SET <I>dataset</I></b> command compiles but never executes so it creates the program data vector (PDV) values. The <b>input (_all_)</b> references the PDV while the <b>(:) modifier</b> works as a separator between the values.
<pre>
<u><b>Contents of the cls.csv file</b></u>
Name,Sex,Age,Height,Weight
Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
Carol,F,14,62.8,102.5
Henry,M,14,63.5,102.5
</pre>
<pre>
data temp;
infile '/temp/cls.csv' dsd dlm = ',' firstobs = 2;
if 0 then set sashelp.class; /* prime the program data vector */
input (_all_) (:); /* read in all the columns from the PDV */
run;
</pre>
<p>Now if you want to add the data to an existing data set, use proc append and you are done..</p>
</html>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7954851561018760025.post-59560446646583402162019-04-27T17:14:00.000-07:002019-04-28T05:56:55.442-07:00X out the X command - use SYSTASK instead<!DOCTYPE html>
<html>
<head>
<STYLE>
table { border-collapse: collapse; border-style: solid; border-width: thin }
th, td { padding: 0 0.25rem; }
th {background: blanchedalmond;}
</STYLE>
</head>
<body>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgA8XzwFfr8aT3sKxYXWUgIWFzXJLNOGj53tUUddHxXkQr4sDVbGzoSlEkE9PHx_p_MJ6uLzDV59qqrd9mdnDdpch6pDMCEeTpV6v567cZWTkGCz4xWaTbjv7V4puz4KXFmORRN-RIUUI4/s1600/vwxyz.png" imageanchor="0" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgA8XzwFfr8aT3sKxYXWUgIWFzXJLNOGj53tUUddHxXkQr4sDVbGzoSlEkE9PHx_p_MJ6uLzDV59qqrd9mdnDdpch6pDMCEeTpV6v567cZWTkGCz4xWaTbjv7V4puz4KXFmORRN-RIUUI4/s1600/vwxyz.png" data-original-width="481" data-original-height="120" /></a></div>
<p>This is an easy one when it comes to best practices. There are times when you may need to shell out to the operating system in order to perform a task that SAS does not otherwise handle.</p>
<p>The easy way to do this is via the <a href="https://documentation.sas.com/?docsetId=hostunx&docsetTarget=n1nx651zrt6pdcn171xjr3xwfxhq.htm&docsetVersion=9.4&locale=en">X command</a>, but is it the best technique to use? The same can be said for the use of <a href="https://documentation.sas.com/?docsetId=hostunx&docsetTarget=p0w085btd5r0a4n1km4bcdpgqibt.htm&docsetVersion=9.4&locale=en">%SYSEXEC and CALL SYSTEM</a> commands. Look at the comparison table below.</p>
<center>
<table border = '1'>
<tr>
<th>Feature</th>
<th>X Command</th>
<th>SYSTASK</th>
</tr>
<tr>
<td>Asynchronous Processing?</td>
<td>No</td>
<td>Yes</td>
</tr>
<tr>
<td>Return Code?</td>
<td>No</td>
<td>Yes</td>
</tr>
</table>
</center>
<p>Be sure to review the SAS documentation on <a href="https://documentation.sas.com/?docsetId=hostunx&docsetVersion=9.4&docsetTarget=p0lzxl2mwndagun1dtxbst9s4jea.htm&locale=en">SYSTASK</a>, here is the syntax in a nutshell:</p>
<p>
<strong>SYSTASK COMMAND</strong> "os command" <WAIT|NOWAIT> <TASKNAME=taskname><br>
<STATUS=statusvar> <SHELL<='shell-command'>> <CLEANUP>;
</p>
<ul>
<li><b>WAIT | NOWAIT</b> determines if the request will be handled asynchronously (NOWAIT the default) or synchronously (WAIT)</li>
<li><b>TASKNAME</b> = taskname uniquely identifies the task - use this with the <a href="https://documentation.sas.com/?docsetId=hostunx&docsetTarget=p0w8zwo1dyssdfn1mjm11dt2v7e2.htm&docsetVersion=9.4&locale=en">WAITFOR</a> command</li>
<li><b>STATUS </b>= statusvar is a unique macro name that stores the status of the task</li>
<li><b>SHELL </b>this specifies that the command should be executed by the OS shell command. You can specify a shell name else the default shell is used.</li>
<li><b>CLEANUP </b>specifies that the task should be removed from the LISTTASK output when the task completes. This allows you to reuse the taskname. <b>NOTE:</b> <i>This option is not available under the Windows operating system.</i></li>
</ul>
<p>
<strong>SYSTASK LIST</strong> <_ALL_ | taskname> <STATE> <STATVAR>;
</p>
<p>
<strong>SYSTASK KILL</strong> taskname <taskname>;
</p>
<p>The two biggest reasons to use SYSTASK are the ability to run processes in parallel (asynchronously) and to get back a status code so you can handle any issues. As noted the X command does not support either of these features. However, SYSTASK can be run asynchronously using NOWAIT or synchronously using the WAIT option. Here is an example of copying three files at once:</p>
<pre>
/* If using Windows or not using the CLEANUP option then do this first */
/* SYSTASK KILL t1 t2 t3; */
SYSTASK COMMAND "cp &in/f1.txt &out/f1.txt" taskname=t1 status=s1 shell cleanup;
SYSTASK COMMAND "cp &in/f1.txt &out/f2.txt" taskname=t2 status=s2 shell cleanup;
SYSTASK COMMAND "cp &in/nofile.txt &out/nofile.txt" taskname=t3 status=s3 shell cleanup;
/* wait on task 1, 2 and 3 to complete before going to line of code that follow it */
WAITFOR _all_ t1 t2 t3;
data _null_;
if &s1 ne 0 then putlog "ERR" "OR: issue copying f1.txt";
if &s2 ne 0 then putlog "ERR" "OR: issue copying f2.txt";
if &s3 ne 0 then putlog "ERR" "OR: issue copying nofile.txt";
run;
</pre>
<p style='color:red'>ERROR: issue copying nofile.txt</p>
<p>UPDATE: After some initial feedback it is important to note that yes the XCMD option must be on for X command, FILENAMEE pipe, %SYSEXEC, CALL SYSTEM, SYSTASK and other commands. I used a simple example of the UNIX cp (copy) command but this could have been handled with the built in SAS function FCOPY(). I have covered the use of <a href="https://kansascode.blogspot.com/2018/04/copydeletemove-files-without-xcmd.html">FCOPY() in this previous blog post</a>.
</body>
</html>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-44391205019907287572019-04-11T07:57:00.001-07:002019-04-19T16:56:40.318-07:00Binary Permutations<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzXOu4vlZW2q5TfQCE2nsowmJY5O0O-UAQ8kEKm-GoaupIyEb4Ge5tXD67_ruhdLXXMMKqDICnlKhsy2CNqU6F3Pzj5MaWITbrhQwsi65oJ_V8Hqs0Bgw-jEEdgwxdbSau7qGzxl7mHk0/s1600/binbump.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzXOu4vlZW2q5TfQCE2nsowmJY5O0O-UAQ8kEKm-GoaupIyEb4Ge5tXD67_ruhdLXXMMKqDICnlKhsy2CNqU6F3Pzj5MaWITbrhQwsi65oJ_V8Hqs0Bgw-jEEdgwxdbSau7qGzxl7mHk0/s1600/binbump.png" data-original-width="373" data-original-height="223" /></a></div>
<p>Would you like to store more than one value in a single variable or column? Doing so can save considerable space resulting in less network traffic and faster throughput. Inspiration and credit for this post came from the SAS Global Forum paper "<a href="https://support.sas.com/resources/papers/proceedings12/063-2012.pdf">Deciphering PROC COMPARE Codes: The Use of the bAND Function</a>" by Hinson and Coughlin.</p>
<p>To keep things simple, consider that we have four books A, B, C and D and need to track every combination of those four. That is someone may have no books or only B and C. To make this all work, assign values using the power of 2 from 0 forward as follows:</p>
<ul>
<li>1 as 2<sup>0</sup> = 1
<li>2 as 2<sup>1</sup> = 2
<li>3 as 2<sup>2</sup> = 4
<li>4 as 2<sup>3</sup> = 8
<li>5 as 2<sup>4</sup> = 16
<li>6 as 2<sup>5</sup> = 32
<li>7 as 2<sup>6</sup> = 64
<li>8 as 2<sup>7</sup> = 128
</ul>
<p>Using <a href="https://www.calculatorsoup.com/calculators/discretemathematics/permutationsreplacement.php">permutation with replacement</a> of 4 objects taken 2 at a time (have it or not) there are 4<sup>2</sup> or 16 possible combinations. This can be stored in a single column via the use of <a href="https://en.wikipedia.org/wiki/Bitwise_operation">bitwise operators</a> such as SAS's <a href="https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=n1s3cxiitukk55n18if88g2djqzs.htm&locale=en">band </a>function (the bitwise logical AND of two arguments). The following code will help better illustrate how this works.</p>
<pre>
data x(drop = value);
value = 15;
do num = 0 to 15;
binary = put(num, binary4.);
if num > 0 then binval = 2 ** (num - 1);
binsum + binval;
if num > 0 then match = band(binval, value);
format binval binsum comma8.;
output;
end;
run;
</pre>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBVHkcRcujus1Y6RXpThW5hGUkjglZOfbYj8Z8oQ-S9PSPQdGTaurZqB_v7XUkU4IR0_yjCOZPgrdOocvB1_-4sbLuYbZoZOINp_IdVA5bQEawSaMBKyFdgA-F0sawJx37BmGkOB_xwHs/s1600/binaryvalues.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBVHkcRcujus1Y6RXpThW5hGUkjglZOfbYj8Z8oQ-S9PSPQdGTaurZqB_v7XUkU4IR0_yjCOZPgrdOocvB1_-4sbLuYbZoZOINp_IdVA5bQEawSaMBKyFdgA-F0sawJx37BmGkOB_xwHs/s1600/binaryvalues.png" data-original-width="241" data-original-height="255" /></a></div>
<p>The binval column contains the value associated with the num column. So if you want books B and C that is the sum of 2 + 4 or 6. If all books were desired, the value is 15 in the binsum column or the cumulative sum of the binval column. You can look at the binary column to see these values in binary format reading back from right to left every combination is covered.</p>
<p>Below is the sample books data set followed by the actual macro function code. The use of low level functions are used to read the data and return the matched column values. This does assume that the data set is in the correct sorted order and only has the same number of rows as needed and no more. The limit to this process is 15 combination which is one less than 2<sup>15</sup> or 32,767</p>
<pre>
data books;
length i 3 book $6;
do i = 1 to 4;
book = cat("Book ", byte(64 + i));
output;
end;
run;
%macro band_permutations(
dsn =
, column =
, value =
, seperator = %str(,)
);
%local dsid position type i binval retval;
%let dsid = %sysfunc(open(&dsn, i)); /* open data set */
%if &dsid %then %do;
%let position = %sysfunc(varnum(&dsid, &column)); /* find position of column */
%let type = %sysfunc(vartype(&dsid, &position)); /* is it 'C'har or 'N'um */
%do i = 1 %to %sysfunc(attrn(&dsid, nlobs)); /* how many rows in data set */
%let binval = %eval(2 ** %eval(&i - 1); /* Calc the binary value */
%if %sysfunc(band(&binval, &value)) > 0 %then %do; /* Did value match */
%if %sysfunc(fetchobs(&dsid, &i)) = 0 %then %do; /* Retrive the row */
%if %length(&retval) = 0 %then
%let retval = %sysfunc(getvar&type(&dsid, &position)); /* read column value */
%else %let retval=&retval &seperator %sysfunc(getvar&type(&dsid, &position));
%end;
%else %sysfunc(sysmsg()); /* Write out message */
%end;
%end;
%let dsid = %sysfunc(close(&dsid)); /* close data set */
%end;
%else %put %sysfunc(sysmsg()); /* unable to open the data set */
/* return the value */
&retval
%mend;
/* below resolves to result = Book B, Book C */
%put result = %band_permutations(dsn=books, column = book, value = 6);
</pre>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7954851561018760025.post-64705607299753207712019-03-28T19:05:00.000-07:002020-01-09T07:07:59.417-08:00Please wait...<p>Keeping users informed on the progress of any activity is not only a courtesy but is also very critical. This is especially true for SAS web based stored processes. I can run the same code and get results back as quickly as three seconds or as long as over a minute. I can not explain this inconsistency therefore I can not expect an end user to be so patient and understanding.</p>
<p>As a result, it is a very good idea to display a message letting the user know that the process is underway and to please wait. Failure to do so can cause the user to submit the same code multiple times.</p>
<p>I wanted to do this without using an image but still be able to display a good looking message with a minimal number of lines of HTML/CSS or JavaScript. To do this I utilized the great free resource, <a href="https://codepen.io/">codepen.</a></p>
<p>Codepen is a cloud based service that allows users to share HTML, CSS and JavaScript code snippets. This well designed interface makes it easy to share ideas and to provide others with code they can use to better illustrate questions. Code can easily be "forked" and saved to your own pen. See below codepen that I <a href="https://codepen.io/arlinadesign/pen/raXmPJ">forked from Arlina Design</a> then made some modifications and saved - you can see it here: <a href="https://codepen.io/tbellmer/pen/aMedyM/">https://codepen.io/tbellmer/pen/aMedyM/</a></p>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhesl0tiQxct4_XAF_RTI9eoU7LDRILes04rsYImMJSemcgli3IuuUQSD0dwAs69vsFLA_xD6NgsG2bYSOBKEdfx35LQn2BSqL_zb4khFWDQ1g-0hHe8pcyPgvoMGynrbbHZbJFFuEhiPg/s1600/pleasewait.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhesl0tiQxct4_XAF_RTI9eoU7LDRILes04rsYImMJSemcgli3IuuUQSD0dwAs69vsFLA_xD6NgsG2bYSOBKEdfx35LQn2BSqL_zb4khFWDQ1g-0hHe8pcyPgvoMGynrbbHZbJFFuEhiPg/s640/pleasewait.png" width="608" height="494" data-original-width="710" data-original-height="577" /></a></div>
<p>Here is how I used the wait message:</p>
<pre>
data _null_;
file _webout;
input;
put _infile_;
datalines4;
<div id = 'pwcontainer' class='pwcontainer'>
<h1 class='ctr'>Please Wait...</h1>
<div class='pwfillme'>
<div class='pwfillme-line'></div>
</div>
</div>
;;;;
run;
/* your SAS code goes here */
data _null_;
file _webout;
input;
put _infile_;
datalines4;
<script>
document.querySelector('#pwcontainer').style.display = 'none';
</script>
;;;;
run;
</pre>
<p>I would love to see SAS Institute provide a service similar to codepen, maybe call it <b>SASPen</b>. Imagine how much easier it would be for someone to provide others actual code to test and modify directly online. Until then, we will just have to, <i>please wait...</i></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-23207878980639264652019-03-19T18:26:00.000-07:002019-03-19T18:26:14.587-07:00Histogram / BoxPlot using GTL<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWe1Ka1b4xkfQqQgKoSkVjFGz7VS69T4p_-TGHEFv_SoQPhCSVuTRNlHLRasbfGy32PKZG01g5cFCYd9RmmuMhnobY9yVWChyphenhyphenTrcdFfjiwgMbqI7n_YhbjWl7r0dxN-nFSxfhDvEWfkmw/s1600/histogram_systolic.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWe1Ka1b4xkfQqQgKoSkVjFGz7VS69T4p_-TGHEFv_SoQPhCSVuTRNlHLRasbfGy32PKZG01g5cFCYd9RmmuMhnobY9yVWChyphenhyphenTrcdFfjiwgMbqI7n_YhbjWl7r0dxN-nFSxfhDvEWfkmw/s1600/histogram_systolic.png" data-original-width="647" data-original-height="487" /></a></div>
<p>I have been a long term fan of <a href="https://blogs.sas.com/content/author/sanjaymatange/">Sanjay Matange</a> and <a href="https://blogs.sas.com/content/author/danheath/">Dan Heath</a> when it comes to the SAS <a href="https://documentation.sas.com/?docsetId=grstatgraph&docsetTarget=titlepage.htm&docsetVersion=9.4&locale=en">graph template language</a> or GTL. I was lucky enough to have met these two individuals in person at the 2018 <a href="https://www.sas.com/en_us/events/sas-global-forum.html">SAS Global Forum</a> in Denver and talk about some of the GTL features. Be sure to check out their SAS blog <a href="https://blogs.sas.com/content/graphicallyspeaking/">Graphically Speaking</a>.</p>
<p>GTL is a unique language with its own syntax that shares very little if anything from the SAS foundation language. However, this is a very powerful piece of software that should not be overlooked.</p>
<p>The above image provides a great sample of what GTL can do. In this case, I am using the SAS supplied SASHELP.HEART data set containing 5,209 observations and creating a histogram along with a subordinate fringe plot of the same data. The top left area of the graph is used to display textual values of key statistical measures. Below the histogram and taking up just 15% of the area is a horizontal box plot.</p>
<p>While a histogram breaks data into bins, a boxplot (aka box and whiskers) is a visual representation of key statistical values. Inside the box is a vertical line that reveals the median while the diamond shape is the mean. The range of the box is from the first quartile or 25th percentile to the third quartile or 75th percentile. The vertical bar on each end is set at the min/max value or the Q1/Q3 value plus 1.5 * the interquartile range. Values beyond that range are true outliers. See below.</p>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJ84WbV6tYC79u3a8C35houoZhtDy1acalaJDE8dry7N7oXXbTXh1bZMmnd5_4PV2g1OWJD05j542cfUFZgrAu-LCxRiRQVRPrVWp2AD3COxrWoT4Si7BhYNSDj3YgR38JwT2PzOGIas8/s1600/boxplot.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJ84WbV6tYC79u3a8C35houoZhtDy1acalaJDE8dry7N7oXXbTXh1bZMmnd5_4PV2g1OWJD05j542cfUFZgrAu-LCxRiRQVRPrVWp2AD3COxrWoT4Si7BhYNSDj3YgR38JwT2PzOGIas8/s320/boxplot.png" width="320" height="290" data-original-width="420" data-original-height="380" /></a></div>
<p>All the code to generate and look over is included below. The key things to understand is that a template is made using define statgraph. Once the template has been created it is referenced along with the data set and rendered using <a href="https://documentation.sas.com/?docsetId=grstatproc&docsetTarget=n194gmpu73h4t5n1vavyo656yj71.htm&docsetVersion=9.4&locale=en">proc sgrender</a>.</p>
<pre>
proc template;
define statgraph distribution;
dynamic
VAR
VARLABEL
NORMAL
TITLE1
;
begingraph;
entrytitle TITLE1;
layout lattice /
columns = 1
rows = 2
rowgutter = 2px
rowweights = (.85 .15)
columndatarange = union
;
columnaxes;
columnaxis /
label = VARLABEL
display = (ticks tickvalues label);
endcolumnaxes;
layout overlay /
yaxisopts = (
offsetmin = .035
griddisplay = on);
layout gridded /
columns = 2
border = true
autoalign = (topleft topright);
entry halign = left "Nobs";
entry halign = right eval(strip(put(n(VAR), comma6.)));
entry halign = left "Min";
entry halign = right eval(strip(put(min(VAR), comma6.)));
entry halign = left "Q1";
entry halign = right eval(strip(put(q1(VAR), comma6.)));
entry halign = left "Median";
entry halign = right eval(strip(put(median(VAR), comma6.)));
entry halign = left "Mean";
entry halign = right eval(strip(put(mean(VAR), comma6.)));
entry halign = left "Q3";
entry halign = right eval(strip(put(q3(VAR), comma6.)));
entry halign = left "Max";
entry halign = right eval(strip(put(max(VAR), comma6.)));
entry halign = left "StdDev";
entry halign = right eval(strip(put(stddev(VAR), comma6.)));
entry halign = left "IQR";
entry halign = right eval(strip(put(qrange(VAR), comma6.)));
endlayout;
histogram VAR / scale = percent;
if (exists(NORMAL))
densityplot VAR /
normal()
name = 'norm'
legendlabel = 'Normal'
;
densityplot VAR /
kernel()
name = 'kern'
legendlabel = 'Kernel'
lineattrs = (
color = red
pattern = dash
);
endif;
fringeplot VAR / datatransparency = .7;
discretelegend "norm" "kern" /
location = inside
across = 1
autoalign = (topright topleft)
opaque = true;
endlayout;
boxplot y = VAR / orient = horizontal;
endlayout;
endgraph;
end;
run;
proc sgrender data = sashelp.heart template = distribution;
dynamic var = "systolic"
varlabel = "Systolic"
normal = "yes"
title1 = "Systolic Blood Pressure"
;
run;
</pre>
</html>Unknownnoreply@blogger.com0Overland Park, KS 66213, USA38.8984667 -94.70188999999999238.8490362 -94.78257099999999 38.9478972 -94.621209tag:blogger.com,1999:blog-7954851561018760025.post-85364836277806395562019-03-03T12:39:00.000-08:002019-03-19T11:44:15.681-07:00udemy.com - best training bargain out there<div class="separator" style="clear: both; text-align: center;"><a href="https://www.udemy.com/" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZXyIpsRHw7ammeYZiv_OmbdNGtYarCBGH6eoUJO5Yv5EjgzHogwpUfBirZe_Jl_7j5oOyt_YwbJaOCWUxdjChtiT5MAjVAvCSnZmEWolXnStEGqQ50vO7fFZJxtqWLCw0NjatMhi0SYc/s320/udemy.png" width="320" height="100" data-original-width="1176" data-original-height="369" /></a></div>
<p>This blog is primarily related to SAS programming. However, there are times when we need to expand our horizons to topics that are tangentially related to SAS. One of those items for me is to better understand and implement HTML and CSS as it relates to using SAS stored processes.</p>
<p>While SAS does supply a built in prompting framework to create interfaces, it is very limiting and inflexible. Of course, the way around this is to roll your own via HTML, CSS and JavaScript as needed. If you utilize the _WEBOUT stream destination in a file statement you can create whatever you like.</p>
<p>So the question becomes, how do you learn HTML and CSS to get started? I live near a very nice and respected community college and they offer a course on HTML/CSS that is 40 hours of classroom material. The cost for this course is $1,199.00 which is not exactly cheap, but may be in contrast to that offered at a major university.</p>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_sQbtnt-8mStm6Xyk6Hw68gvrU8ISKgdvvKIpFsLRKLq_pCUR0HYK5SkaeqGvhD1hGVkPBT0qkoa3q6neQ-OgUeONpnA3qtlUj-LC6K-LGn9K9r7nCykd27Tjf6xsgUvjIjWN5JHgAwQ/s1600/jccc_htmlcss.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_sQbtnt-8mStm6Xyk6Hw68gvrU8ISKgdvvKIpFsLRKLq_pCUR0HYK5SkaeqGvhD1hGVkPBT0qkoa3q6neQ-OgUeONpnA3qtlUj-LC6K-LGn9K9r7nCykd27Tjf6xsgUvjIjWN5JHgAwQ/s400/jccc_htmlcss.png" width="400" height="172" data-original-width="1077" data-original-height="463" /></a></div>
<p>As Mick Jagger so eloquently put it in the song Street Fighting Man, "Well, then what can a poor boy do Except to sing for a rock 'n' roll band 'Cause in sleepy London town There's no place for a street fighting man No Hey!".
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqID2XamOy4BVKkYZmXghh6GdoraLaZMymHP8-3Wo2emwGUXnB-Xxzv2uoC1uQGRPwOkFeLG90zGMDsxv3fhRnZA8Cl9giUer8KqvYdTaOW-clsP2qGRYHAu1ImYRamyjgWVzaYU-Pw60/s1600/jagger.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqID2XamOy4BVKkYZmXghh6GdoraLaZMymHP8-3Wo2emwGUXnB-Xxzv2uoC1uQGRPwOkFeLG90zGMDsxv3fhRnZA8Cl9giUer8KqvYdTaOW-clsP2qGRYHAu1ImYRamyjgWVzaYU-Pw60/s320/jagger.png" width="320" height="185" data-original-width="688" data-original-height="397" /></a></div>
<p>The answer is the online course <a href="https://www.udemy.com/modern-html-css-from-the-beginning/">Modern HTML & CSS From The Beginning (Including Sass) by Brad Traversy via udemy.com</a>. Brad Traversy is a truly amazing talent who has put out over 650 free videos on his youtube channel - <a href="https://www.youtube.com/channel/UC29ju8bIPH5as8OGnQzwJyA">Traversy Media that has over 600,000 followers</a>. This particular course is 21 hours of material and is rated 4.8 out of 5 based on 1,173 reviews. If you look, you will be able to purchase this $149.99 class for as little as $11.99 which is 99% less than the week long class mentioned above. The nice thing is that you can take this course on your own schedule, as time permits - it does not expire. Udemy.com also offers hundreds of other courses and is just a fantastic bargain and very highly recommended.</p>Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-7954851561018760025.post-26616326652901829342018-12-19T18:42:00.000-08:002018-12-20T07:10:49.742-08:00Macro %then semicolon<html>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCDnWmJehPI9CCPL3nnJcYfwiTqlUa5dcfUageuV1ckEnI-ezE4b6ynUTBMSU8c-Fl3NoQHU-7rA2MYB_SRifYZYzRaOQjrm4XboOmGFpo7QyD-hRk5fbEYs9n3W8jWOnNF5DnKCuDRy4/s1600/semicolon.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCDnWmJehPI9CCPL3nnJcYfwiTqlUa5dcfUageuV1ckEnI-ezE4b6ynUTBMSU8c-Fl3NoQHU-7rA2MYB_SRifYZYzRaOQjrm4XboOmGFpo7QyD-hRk5fbEYs9n3W8jWOnNF5DnKCuDRy4/s640/semicolon.jpg" width="640" height="158" data-original-width="1291" data-original-height="318" /></a></div>
<p>As a creature of habit, I typically write <a href="https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n18fij8dqsue9pn1lp8436e5mvb7.htm&docsetVersion=9.4&locale=en">%IF %THEN/%ELSE statements</a> using <a href="https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=p0ri72c3ud2fdtn1qzs2q9vvdiwk.htm&docsetVersion=9.4&locale=en">%DO</a> blocks. However, is a %DO block needed for a one line statement? This blog post answers that question via a series of test conditions. Please understand that this is a very simple example program to illustrate the problem that was experienced.</p>
<h1>Test 1 single semicolons</h1>
<pre>
%macro test();
data mydsn;
%if &sysver = 9.4 %then x = 1;
%else x = 0;
date = date();
run;
%mend;
%test();
80 date = date(); run;
____
22
<font color=red>ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=. </font>
</pre>
<p>That seems odd, but I guess I need to add another semicolon to each of the %IF and %ELSE lines to terminate the macro language statements. Here are the results of that attempt.</p>
<h1>Test 2 - using two semicolons</h1>
<pre>
73 %macro test();
74 data mydsn;
75 %if &sysver = 9.4 %then x = 1;; /* notice use of two semicolons */
76 %else x = 0;; /* notice use of two semicolons */
<font color=red>ERROR: There is no matching %IF statement for the %ELSE.
ERROR: A dummy macro will be compiled.</font>
77 date = date();
78 run;
79 %mend;
80 %test();
</pre>
<h1>Test 3 - two semicolons on the %ELSE line</h1>
<pre>
%macro test();
data mydsn;
%if &sysver = 9.4 %then x = 1; /* one semicolon here */
%else x = 0;; /* two semicolons here */
date = date();
run;
%mend;
%test();
<font color=blue>NOTE: The data set WORK.MYDSN has 1 observations and 2 variables.</font>
</pre>
<p>While the use of one semicolon on the first line and two on the second macro language line works, I find it confusing. In the name of ease of reading and maintaining, I still prefer the use of %DO blocks.</p>
<h1>Test 4 - %DO blocks</h1>
<pre>
%macro test();
data mydsn;
%if &sysver = 9.4 %then %do;
x = 1;
%end;
%else %do;
x = 0;
%end;
date = date();
run;
%mend;
%test();
<font color=blue>NOTE: The data set WORK.MYDSN has 1 observations and 2 variables.</font>
</pre>
</html>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7954851561018760025.post-72278620406560588592018-09-22T13:42:00.001-07:002018-09-22T14:43:38.019-07:00Enterprise Guide Keyboard Macros<html>
<p>Enterprise Guide (EG) supports keyboard macro abbreviations to write out snippets of code directly into a program editor. This makes it easy to supply preset code in a consistent manner without having to remember the syntax.</p>
<p>From the EG menu, select <b>Program | Add Abbreviation Macro…</b> or use <b>Ctrl+Shift+A</b>. Use the below dialog to enter your own abbreviation (e.g. _sql) and associated code in the <b>Text to insert:</b> section.</p>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHz71w-o0WWrGLoxluODp4G7eaWHb9ZkKZsCZqz_1gJF_jQLzu164WliDHtBxP3lKoIN5RC90VWXyUspdi10d2sxq5itz5mHh3MpYlgX8HR0gnhPjB-_38FKfuNiV5c1KSvvEpIZtCxoc/s1600/addabbr.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHz71w-o0WWrGLoxluODp4G7eaWHb9ZkKZsCZqz_1gJF_jQLzu164WliDHtBxP3lKoIN5RC90VWXyUspdi10d2sxq5itz5mHh3MpYlgX8HR0gnhPjB-_38FKfuNiV5c1KSvvEpIZtCxoc/s400/addabbr.jpg" width="400" height="283" data-original-width="264" data-original-height="187" /></a></div>
<p>You can delete, edit, rename or export existing abbreviations or import existing keyboard macro functions (.KMF extension) by selecting <b>Program | Editor Macros | Macros</b> from the menu.</p>
<p>Below is the resulting output of the <b>_hash</b> keyboard macro abbreviation.</p>
<pre>
data outdsn;
if 0 then set hashdsn; /* Prime the PDV */
dcl hash h(dataset: "hashdsn");
h.definekey('keyvar');
h.definedata('keyvar1', 'datavar1');
h.definedone();
do until(eof);
set otherdsn end = eof;
if h.find() ne 0 then call missing(keyvar1, datavar1);
output;
end;
stop;
run;
</pre>
</html>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-242071535912242422018-09-14T14:34:00.001-07:002018-09-16T13:12:30.923-07:00ColumnLength macro function<p>There are times when you need to dynamically obtain the data type and length of a SAS data set column. This occurred for me when I was writing a generic post process following a proc means with a conditional class statement. I need to know the data type and length of the class variable so that it can be assigned in the ATTRIB statement.</p>
<p>The below code utilizes the %isblank macro function that <a href="https://kansascode.blogspot.com/2015/05/four-short-macro-functions.html">can be found here</a>. The results of the function will be a dollar sign ($) if it is a character data type followed by the length of the data set column.</p>
<p>The table reveals the various results of calling this macro:</p>
<table border='1'>
<tr bgcolor = "lightblue">
<th>Call</th>
<th>Result</th>
<th>Message</th>
</tr>
<tr>
<td>%columnlength(dsn=class, column = age);</td>
<td>8</td>
<td></td>
</tr>
<tr>
<td>%columnlength(dsn=class, column = name);</td>
<td>$8</td>
<td></td>
</tr>
<tr>
<td>%columnlength(dsn=classx, column = name);</td>
<td></td>
<td>ERROR: The data set classx does not exist.</td>
</tr>
<tr>
<td>%columnlength(dsn=class);</td>
<td></td>
<td>ERROR: You must pass in a value for column.</td>
</tr>
<tr>
<td>%columnlength(dsn=class, column = namex);</td>
<td></td>
<td>ERROR: Could not find column: namex</td>
</tr>
</table>
<p>Here is an example of how I used this. I have a macro that includes a proc means/summary with a conditional class statement. After the process is finished I want to control the formatting, order and even add a new column. So this is my code snippet</p>
<pre>
data &dsn;
attrib
yyyymm length = $6
%if not %isblank(&classvar( %then %do;
&classvar length = %columnlength( dsn = &dsn, column = &classvar)
%end;
...
</pre>
<pre>
%macro columnlength(
dsn =
, column =
);
%local
dsid
position
type
retval
;
%if not %sysfunc(exist(&dsn)) %then %do;
%put %str(E)RROR: The data set &dsn does not exist.;
%return;
%end;
%if %isblank(&column) %then %do;
%put %str(E)RROR: You must pass in a value for column.;
%return;
%end;
%let dsid = %sysfunc(open(&dsn.));
%if &dsid %then %do;
%let position = %sysfunc(varnum(&dsid, &column));
%if &position = 0 %then %put %str(E)RROR: Could not find column: &column.;
%else %do;
%if %sysfunc(vartype(&dsid, &position)) = C %then %let type = $;
%let retval = &type%sysfunc(varlen(&dsid, &position));
%end;
%let dsid = %sysfunc(close(&dsid));
%end;
&retval
%mend;
</pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-42394918886176630792018-09-01T08:58:00.001-07:002018-09-03T13:31:46.318-07:00Stacking datasets<p>A frequent task is to stack SAS datasets segregated by a time period such as a year/month over time to analyze trends. An easy way to do that is to use the colon modifier as a wildcard at the end of a known pattern. That technique is used to read in data sets starting with x_ or in the second example by the more specific x_2017 and x_2018 patterns.</p>
<p>Sometimes you may have this type of yyyymm pattern, but only want to read in for a set period of time such as 201711 to 201802. The third example uses a macro to handle that scenario. Of course this macro can be augmented to support a where clause or to drop/keep a list of variables.</p>
<pre>
proc datasets lib = work nolist kill;
quit;
data x_201711 x_201712 x_201801 x_201802;
call streaminit(1);
do i = 1 to 4;
state = fipnamel(ceil(rand('uniform')*50));
if i = 1 then output x_201711;
else if i = 2 then output x_201712;
else if i = 3 then output x_201801;
else if i = 4 then output x_201802;
end;
run;
data stack1;
set x_:;
run;
data stack2;
set x_2017: x_2018: ;
run;
%macro stackds(
dsnin=
, dsnout=
, startyyyymm=
, endyyyymm=
, where_clause=
, drop=
);
%let start = %sysfunc(inputn(&startyyyymm.01, anydtdte9.));
%let end = %sysfunc(inputn(&endyyyymm.01, anydtdte9.));
%let diff = %sysfunc(intck(month, &start, &end));
data &dsnout;
set
%do i = 0 %to &diff;
%let yyyymm = %sysfunc(putn(%sysfunc(intnx(month,&start,&i,b)),yymmn6.));
&dsnin&yyyymm
%end;
;
%if &where_clause ne %then %do;
where &where_clause;
%end;
%if &drop ne %then %do;
drop &drop;
%end;
run;
%mend;
%stackds(
dsnin = x_
, dsnout = stack3
, startyyyymm = 201711
, endyyyymm = 201802
, where_clause = state = 'Utah'
, drop = i)
</pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-6950558618933732652018-08-26T12:04:00.001-07:002018-08-26T12:08:22.814-07:00SQL - Joining tables witout a common key<p>It is always best to allow a remote database to handle as much work as possible and return as little data as possible over the network. SQL is a very universal language used by most, if not all database systems.</p>
<p>Recently I had occasion to extract data from SQL Server and found it most efficient to utilize its system tables to do just that. In my case, I wanted both the total record count as well as the number of character and numeric data types. While this information is available using SAS dictionary tables from an ODBC libname statement, it was taking up to four minutes to return the data.</p>
<p>The code below creates two derived tables that contain the desired information. The problem was that there is not a common key to perform the required join. While a cross-join is possible, those joins in PROC SQL write a WARNING to the SAS log which is not allowed in my parselog macro.<p>
<p>The solution was to join the derived table using the ON 1 = 1 syntax. This worked and returned the correct answer in under one second which is much faster than the four minutes using a libname.</p>
<pre>
proc sql;
connect to odbc(dsn=mydsn authdomain="myauth");
create table results as
select totalrows
, char_cnt
, num_cnt
from connection to odbc(
select cnt.totalrows
, col.char_cnt
, col.num_cnt
from
( select sum(p.rows) as totalrows
from sys.tables t
join sys.partitions p
on t.object_id = p.object_id
and p.index_id IN (0, 1)
where t.name = 'balancechanges'
) as cnt
join
( select
sum(iif(ty.name in('nvarchar', 'varchar', 'char'
, 'nchar', 'text'), 1, 0)) as char_cnt
, sum(iif(ty.name in('nvarchar', 'varchar', 'char'
, 'nchar', 'text'), 0, 1)) as num_cnt
from sys.tables t
join sys.columns c
on t.object_id = c.object_id
join sys.types ty
on c.user_type_id = ty.user_type_id
where t.name = 'balancechanges'
) as col
<b>on 1 = 1</b>
);
disconnect from odbc;
quit;
</pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7954851561018760025.post-15039431866765164642018-08-14T17:12:00.000-07:002018-08-15T06:34:03.893-07:00%juliandate macro function<html>
<p>Astronomers use it and so too does the Google search daterange operator (e.g. daterange:2436900-2436934). The Julian calendar starts at noon universal time (UT), 1 January 4713 BCE (before current era or BC), which is before recorded history. In the business world, Julian date has represented the year and sequential day of that year, now known as an ordinal date. Ever since 1988, the ISO 8601 defines current Julian date usage as astronomers use it.</p>
<p>SAS uses the Gregorian calendar which starts on 15 October 1582 to take into account the inaccuracies of the Julian calendar. According to <a href="https://en.wikipedia.org/wiki/Gregorian_calendar">this article</a>, the Gregorian calendar was developed as a refinement of the Julian calendar, shortening the average year by 0.0075 days to stop the drift of the calendar with respect to the equinoxes. To deal with the 10 days of accumulated drift, the date was advanced so that 4 October 1582 was followed by 15 October 1582.</p>
<p>Of course, SAS uses January 1, 1960 as its days zero (0) so adding 2436934.5 days creates the Julian date. SAS does not have any function to return the ISO 8601 or Julian date rather all Julian type functions from SAS relate to ordinal dates. And according to this <a href="https://support.sas.com/kb/38/811.html">SAS knowledge base link</a>, "there are no plans to change the names or functionality" of the SAS functions, informats or formats related to Julian dates.
<p>So if you ever need to obtain a Julian date, just use the below code. The Julian date for 14 August 2018 is 2458344.5.</p>
<pre>
%macro juliandate(date = %sysfunc(date()));
%sysevalf(&date + 2436934.5)
%mend;
</pre>
</html>Unknownnoreply@blogger.com0