topic List Minus or Subtraction in Discussions

List Minus or Subtraction

pmroz — Tue, 07 Sep 2010 15:17:06 GMT

Hello all,

Is there a way in JMP to subtract one list from another, kind of like a SQL MINUS? For example I want to do the following:

biglist = {"a", "b", "c", "d", "e", "f", "g"};

shortlist = {"a", "b", "c"};

newlist = biglist MINUS shortlist;

Newlist would be {"d", "e", "f", "g"};

Thanks,
Peter

Re: List Minus or Subtraction

pmroz — Tue, 07 Sep 2010 16:04:09 GMT

Well I figured something out:

style="font-size: 10pt; font-family: "Courier New"; color: black;">biglist

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
{
style="font-size: 10pt; font-family: "Courier New"; color: purple;">"a"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"b"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"c"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"d"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"e"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"f"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"g"
style="font-size: 10pt; font-family: "Courier New"; color: black;">}
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">shortlist

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
{
style="font-size: 10pt; font-family: "Courier New"; color: purple;">"a"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"b"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"c"
style="font-size: 10pt; font-family: "Courier New"; color: black;">}
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">newlist

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
{}
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">for
style="font-size: 10pt; font-family: "Courier New"; color: black;">
(i

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: teal;">1
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
i
style="font-size: 10pt; font-family: "Courier New"; color: navy;"><
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">nitems
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">biglist)
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
i
style="font-size: 10pt; font-family: "Courier New"; color: navy;">++,
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">if
style="font-size: 10pt; font-family: "Courier New"; color: black;">
(
style="font-size: 10pt; font-family: "Courier New"; color: navy;">!
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">contains
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">shortlist
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
biglist[i])
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">

style="font-size: 10pt; font-family: "Courier New"; color: green;">//
then
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">insertinto
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">newlist
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
biglist[i])
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">
)
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">)
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">

But it would be great if there were an easier way.

I'm actually trying to see the difference between two datasets. I can do a difference between two ID-value columns, but ideally I'd like to see all the rows in one dataset that are not in the other. A SQL MINUS would be perfect!

Regards,

Peter

Re: List Minus or Subtraction

aim — Wed, 08 Sep 2010 03:38:31 GMT

The SQL MINUS functionality can be simulated in JMP by data table "Update" method. It works like this:

- take two data tables, which have some common data

- add a dummy column with all values as "1" to first dt;

- add a dummy column with the same name to the second dt, but this time with all values as "0";

- "update" first dt with second dt by matching columns (excluding dummy ones!)

After the update, dummy column in first table will have "0" for rows that are matched to the second dt, and "1" for rows that are different. What is left at this point is to subset all this rows with dummy value "1" to get data table with differences.

Here is a function, which can get difference between two data tables using method described above, and a couple examples of using it.

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">/////////////////////////////////////////////////////////////////////////////////
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Function
jmp_SQL_MINUS
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//performs
substraction on data sets, similar to "SQL MINUS"
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//
style="mso-spacerun: yes"> result_dt = dt1 - dt2
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//MUST have
at least one column with similar name in BOTH data tables
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Arguments:
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//
style="mso-spacerun: yes"> dt1 - first table
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//
style="mso-spacerun: yes"> dt2 - second table
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//
style="mso-spacerun: yes"> new_table_name - name of the resulting
table
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">jmp_SQL_MINUS

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Function
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
{dt1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
dt2
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
new_table_name}
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">
style="mso-spacerun: yes">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Get list
of columns with matching names between 2 tables
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 3">
col_str
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> col_str

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">||
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">":Name(\!""
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">||
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
cl2[ii]
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">||
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">"\!")==:Name(\!""
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">||
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
cl2[ii]
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">||
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">"\!")"
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">||
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">If(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">ii
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">==
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">dt2_nc
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">""
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">","
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">)
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 2">
)

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Build
"update" expression using template
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Create
helper column with unique name
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Run
"update" expression. After this step helper column shows "0" for values that
are matched between two tables,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//and "1"
for values that are different
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Extracting
list of values that are not matched
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Cleanup
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Outputting
result
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Example of
usage
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//open "big
table"
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">dt

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Open
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">"$SAMPLE_DATA/Big
Class.jmp"
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//subset of
"big table", only 10 rows and a couple of columns
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">sub_dt

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Data
Table
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">"Big
Class"
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> )

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'"><<
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
Subset(

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//result
data table is a difference between "big" table and its subset
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">result_dt

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
jmp_SQL_MINUS( dt
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
sub_dt
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">"Difference
between dt and sub_dt"
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Second
example
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Open big
data table
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">dt

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Open
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">"$SAMPLE_DATA/Equity.jmp"
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//Create
data table with items that needs to be excluded
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">sub_dt

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">New
Table
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">"Exclude
Items"
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">//result
data table has only filtered items
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">result_dt

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
jmp_SQL_MINUS( dt
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
sub_dt
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">"Filtered"
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="MARGIN-BOTTOM: 0pt">

Re: List Minus or Subtraction

Wed, 08 Sep 2010 20:02:19 GMT

This isn't exactly the same problem, but it also would be worth taking a look at the thread below, which describes the usage of associative arrays to do something similar: http://support.sas.com/forums/thread.jspa?messageID=34183薇

Re: List Minus or Subtraction

aim — Thu, 09 Sep 2010 23:08:54 GMT

The associative arrays can be quite useful, but not when your data have duplicate values that need to be preserved:

Say, there are two lists:

list1 = {"a", "b", "c", "d", "e", "f", "g", "a", "b", "c"};

list2 = {"d", "e", "f", "g", "ss"};

Subtracting list2 from list1 using a direct method results in list: {"a", "b", "c", "a", "b", "c"}, while doing this using associative array method results in list: {"a", "b", "c"}. So, which method to use depends on the nature of data in lists.

If you deal with large 1D lists with duplicates, here is the faster version of the list subtraction routine:

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">List_Minus

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Function
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> (
{list1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2}
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1"> {Default
Local}
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">While
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">N
Items
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> list2
)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">>
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">0
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 2">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Remove
From
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">As
List
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Loc
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2[
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">]
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> )
) )
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 2">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Remove
From
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">As
List
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Loc
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2[
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">]
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> )
) )
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">
)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1"> list1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

To compare how much faster, here is the test routine, and its results:

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">List_Minus

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Function
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> (
{list1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2}
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">While
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">N
Items
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> list2
)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">>
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">0
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 2">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Remove
From
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">As
List
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Loc
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2[
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">]
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> )
) )
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 2">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Remove
From
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">As
List
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Loc
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2[
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">]
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> )
) )
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">
list1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">slow_List_Minus

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Function
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
{list1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2}
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1"> newlist

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
{}
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">For
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> i

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> i

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'"><=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">N
Items
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> list1
)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
i
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">++,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 2">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">If
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">!
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Contains
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list2
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list1[i] )
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-spacerun: yes">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 3">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Insert
Into
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
newlist
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
list1[i] )

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 2">
)

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1"> newlist

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'">/////////////////////////////////////////////////////////////////
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">vv

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">As
List
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">J
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">100000
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Random
Integer
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">100
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">)))
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">vsm

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">As
List
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">J
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1000
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Random
Integer
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">90
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">100
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">)))
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">t1

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Tick
Seconds
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">()
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1"> newlist

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
List_Minus(vv
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">vsm)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">t2

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Tick
Seconds
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">()
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Show
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">"List_Minus
time:"
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Round
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> t2

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">-
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
t1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">3
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
))
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1"> newlist

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
slow_List_Minus(vv
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">vsm)
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Show
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: purple; FONT-FAMILY: 'Courier New'">"slow_List_Minus
time:"
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Round
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> t2

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">-
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
t1
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">3
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
))
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

Here is the script output (on modestly fast PC):

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">"List_Minus
time:"
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">Round(::t2 -
::t1, 3):0.217
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">"slow_List_Minus
time:"
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">Round(::t2 -
::t1, 3):229.75
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

Using associative array would be fast, but would result in a list 89 items long (numbers from 1 to 89).