topic How to not get duplicate results. in SAS Programming

How to not get duplicate results.

mrdlau — Sat, 24 Mar 2018 04:25:01 GMT

I have a spreadsheet attached below of 2 tables that I am trying to join. but I"m having trouble with getting duplicate results.

I'm trying to join left join T2 to T1 (join by "ID"), and return a table that looks like T3....but without the duplicate "costs". I only want "Cost" to be returned once per ID.

I tried the statement below, but it actually wont work for me because during the query process, there's other tables involved and the final result might not include 'Phone 1'. I really just need the cost to be returned once to any row that is available

(Case when phone > 1 then 0 else Cost end)

another option is to use the row_over and partition function, but I'm wondering if there is an easier/simpler logic that I can use instead.

I'd like to stay in proc sql if possible

Re: How to not get duplicate results.

PGStats — Sat, 24 Mar 2018 04:32:32 GMT

So T3 is not what you want. What do you want that't not in T2?

Re: How to not get duplicate results.

art297 — Sat, 24 Mar 2018 04:54:51 GMT

Couldn't you use:

           case
             when phones gt min(phones) 0
             else cost
           end
         group by id

Art, CEO, AnalystFinder.com

Re: How to not get duplicate results.

mrdlau — Sat, 24 Mar 2018 05:03:19 GMT

My columns in T3 should have Phone, ID, and cost....but I only want cost to appear once for each ID.

Re: How to not get duplicate results.

PGStats — Sat, 24 Mar 2018 05:12:37 GMT

I would try this:

proc sql;
create table T3 as
select
    a.ID,
    a.Phones,
    t2.cost
from 
    (select ID, max(Phones) as Phones from T1 group by ID) as a left join 
    T2 on a.ID = T2.ID;
quit;

(untested)

Re: How to not get duplicate results.

art297 — Sat, 24 Mar 2018 14:07:20 GMT

I think you are looking for:

proc sql;
  create table t3 as
    select a.ID,a.Phones,
           case
             when phones gt min(phones) then 0
             else b.cost
           end as Cost
      from t1 a left join t2 b
        on a.ID=b.ID
          group by a.ID
            order by a.ID,a.Phones
  ;
quit;

Art, CEO, AnalystFinder.com

Re: How to not get duplicate results.

mrdlau — Sat, 24 Mar 2018 20:22:15 GMT

Thanks. This works for me.....but now that I apply that to my code, I'm wondering if I can use data step just for that section. My query is long and if I use min/max in my proc sql;, I need to do a huge group by.

that's fine, but I think a separate data step just to remove the duplicates might be a bit cleaner. Is it possible?

Here's what I had, but I have, but it's not working because it's evaluating it row by row, rather than each group. I,e I'm getting the actual cost on each row

DATA WORK.T3;
SET WORK.T3;
IF Phones > MIN(phones) THEN cost 0; ELSE cost = cost;
RUN;

I even tried adding a "by ID" but it didn't work.

Re: How to not get duplicate results.

art297 — Sat, 24 Mar 2018 20:31:27 GMT

If your current table3 has the first record for each ID having the lowest phone value, then I'd use a data step, using ID as the by variable, and use:

IF not first.ID THEN cost=0;

Art, CEO, AnalystFinder.com