topic Re: Custom Dedupe and Sorting By Two Columns in SAS Enterprise Guide

Custom Dedupe and Sorting By Two Columns

lawatkey — Thu, 22 Oct 2020 15:39:10 GMT

Hi, so I have a table that looks like this:

Field 1 Field 2

1	7
1	8
1	9
2	7
2	8
2	9
3	7
3	8
3	9

I want to be able to sort this table in a way that makes it look like:

1	7
2	8
3	9

I have a working for solution for when this happens 2 or 3 times, but I was wondering if there was a clever shorter solution than:

1. Creating a table A that sorts by fields 1 and 2

2. Deduping table A by field 1

3. Deduping table A by field 2

4. Creating a table B that sorts by fields 1 and 2, that does not have the field 1 and field 2 found in table A.

5. Repeat this as many times as necessary to get a table A that has "1, 7", table B with "2, 8" and table C with "3, 9"

6. Union the final tables together.

Re: Custom Dedupe and Sorting By Two Columns

PGStats — Thu, 22 Oct 2020 16:23:52 GMT

Do it in a single step with hash objects:

data have;
input a b;
datalines;
1 7
1 8
1 9
2 7
2 8
2 9
3 7
3 8
3 9
;

data want;
set have;
if _n_ = 1 then do;
    dcl hash ha();
    ha.definekey("a");
    ha.definedone();
    dcl hash hb();
    hb.definekey("b");
    hb.definedone();
    end;
if ha.check() and hb.check() then do;
    output;
    rc = ha.add();
    rc = hb.add();
    end;
drop rc;
run;

proc print data=want; run;

Re: Custom Dedupe and Sorting By Two Columns

ballardw — Thu, 22 Oct 2020 16:28:12 GMT

If your actual data has any other variables at all you should provide some examples and what is done with them.

Some details:

Are the values always the same 1,7 2,8 3,9

or are the field values always the same between the Field1 but could be different?

Are the field1 values always sequential?

Are the field2 always sequential? (some programming approaches that might work for sequential won't work when not)

Are ALL the field2 values present for ALL field1 values?

Are there the same number of values for field1 and field2?

For your example this does what is asked.

data have;
input Field1  Field2;
datalines;
1	7
1	8
1	9
2	7
2	8
2	9
3	7
3	8
3	9
;

proc sort data=have(keep=field1) out=temp1
     nodupkey;
     by field1;
run;

proc sort data=have(keep=field2) out=temp2
     nodupkey;
     by field2;
run;

data want;
   merge temp1 temp2;
run;

If the number of values of field1 and field2 do not match this likely doesn't work. You need to provide example of what should happen in this case.

Note: any other variables are excluded.

Re: Custom Dedupe and Sorting By Two Columns

Ksharp — Fri, 23 Oct 2020 11:19:14 GMT

data have;
input a b;
datalines;
1 7
1 8
1 9
2 7
2 8
2 9
3 7
3 8
3 9
;
data want;
n+1;
 do i=1 by 1 until(last.a);
  set have;
  by a;
  if i=n then output;
 end;
drop i n;
run;