Fetching records with unique value

[Sivanandam]

Hi All,

In the below screen shot there are two columns store and store1.

In store column there are only two unique store numbers(2648,6543) but corresponding store1 will have different store numbers

which  will be same for both the store(2648,6543).

i need to select  unique store and it corresponding store1 where store1 should not be same for  both store.

Need as follow:

Store     store1

2648     5582

6453     6040

Thanks,

Siva.

[Sivanandam]

Sorry screen shot is not displaying here.

          store        store1 

           2648          5582 

           2648          6040 

           2648          6453 

           2648          2970 

           2648          5582 

           2648          2523 

           2648          5217 

           2648          2648 

           2648          6138 

           2648          2523 

           2648          2492 

           2648          2871 

           2648          2205 

           2648          2492 

           2648          2513 

           2648          3302 

           2648          6138 

           2648          5273 

           2648          2970 

           2648          6040 

           2648          2871 

           2648          6453 

           2648          2513 

           2648          3251 

           2648          3251 

           2648          2205 

           2648          3302 

           2648          2648 

           2648          5217 

           2648          5273 

           6453          5582 

           6453          3251 

           6453          3251 

           6453          2492 

           6453          5273 

           6453          2492 

           6453          2205 

[ieva]

You mean you want to leave just unique combinations and with additional rule that store1 should not be the same for both stores? In case if it is the same, which store combination (with 2648 or 6453) you want to keep?

This could work:

*get unique combinations;

proc sort nodupkey data=yourdata;

by store store1;

run;

* keep each store1 just one time;

proc sort nodupkey data=yourdata;

by store1;

run;

[Sivanandam]

Further to clarify my requirement.

the numbers present in the modelid  column belong to the group 10549

and the number present in the store column belong to 10549 group.

i need to select unique numbers from the store column i.e

6453 and 2648  and its model id  where it belongs to the corresponding group i.e 10549.

The modelid which we are fetching should not present in the other store.

ex:

O/p as follows

Group       store      Modelid

10549     6453          5582(It can be any of the number in Modelid column but it should blong to the group 10549     2648            2871

10548     6453          3216

10548     9648          2205

I/p

   group         store        Modelid           

   10549          6453          5582           

   10549          6453          5217           

   10549          6453          2871           

   10549          6453          6138           

   10549          6453          3302           

   10549          6453          2648           

   10549          6453          5273           

   10549          6453          2513           

   10549          6453          6040           

   10549          6453          2970           

   10549          6453          6453           

   10549          2648          3302           

   10549          2648          2648           

   10549          2648          5217           

   10549          2648          2513           

   10549          2648          5273           

   10549          2648          2871           

   10549          2648          5582           

   10549          2648          2492           

   10549          2648          6040           

   10549          2648          2970           

   10549          2648          6138           

   10549          9648          3216           

   10548          9648          6453           

   10548          9648          3251           

   10548          9648          2205           

   10548          9453          6453           

   10548          9453          3251           

   10548          9453          2205           

   10548          9453          3216           

[ballardw]

Assuming I understand that you are removing both occurence of the stores with duplicate Modelid within a Group this might do what you're asking for. Change DATASET below your data, changed REDUCED to your desired output table. If you need other variables not mentioned then the results below will need to be remerged with the original data to get them.

proc sql;  

create table reduced as  

select * from dataset  

except  

select *   from (select a.group, a.store , a.Modelid    from dataset as a join dataset as b on a.group=b.group and a.modelid=b.modelid and a.store ne b.store)   ;

quit;

[Sivanandam]

14   proc sql;                                                       

15   create table reduced as select * from xx.table2                 

16   except                                                          

17   select * from(select a.group,a.store,a.store1 from xx.table2    

18   as a join xx.table2 as b                                        

19   on a.group=b.group and a.store1=b.store1 and a.store ne b.store);

NOTE: Table WORK.REDUCED created, with 0 rows and 3 columns.         

where am not getting any output

[LinusH]

I assume that there is an error in you output example, line 3 (that line does not exist in the input). Assuming that line should read :     10548          9453          3216, I think the following code might do what you want:

proc sql;

select group, store, modelid, count(*) as no

from inputData

group by group, modelid having no eq 1

;

quit;

/Linus

[Ksharp]

Whether the output is what you want?

data temp;
input store        store1 ;
cards;
           2648          5582 
           2648          6040 
           2648          6453 
           2648          2970 
           2648          5217 
           2648          5273 
           6453          5582 
           6453          3251 
           6453          3251 
           6453          2492 
           6453          5273 
;
run;
proc sql noprint;
 create table want as
  select distinct *
   from temp
    group by store1
     having count(distinct store) eq 1
      order by store;
quit;

Ksharp

[Sivanandam]

Hi Sharp,

i need th e output as follows

store   store1

2648     5582

6453     3251

Thanks,
Siva

[Ksharp]

Not quit sure what you want.

Do ballardw and Linus 's code match your demand?

[Sivanandam]

yes sharp..Till now i didn't get

[Ksharp]

Hi. I still can not understand what is your intention.

Could you give another example and explain what your logic is and what output you need.

Ksharp

[Sivanandam]

Hi Sharp,

Sorry for the delayed response..

Please find my requirements in detail:

There are three column named group,newstore,modelid

My inputs are as follows:

Group          Newstore          Modelid

   10549          6453               5582           

   10549          6453               5217           

   10549          6453               2871  

   10549          2001               5582           

   10549          2001               5217           

   10549          2001               2871

   10548          9648               6453           

   10548          9648               3251           

   10548          9648               2205           

   10548          9453               6453           

   10548          9453               3251           

   10548          9453               2205     

Here the newstore 6453 and 2001 belong to the same group 10549.

For 10549 group  there will be three model id (5582,5217,2871) . it varies based up on group number.

i need to selce the unique newstore and its group,modelid but the model id  shoul not contain duplicates.

My output as follows:

`Group          Newstore          Modelid

    10549          6453               5582   

    10549          2001               5217

     10548          9648               6453

     10548          9453               2205

here in output you can see the unique  newstore value and its corresponding group and modelid.

where modelid will not have any duplicates.

Hope my requirement is clear now it .I need to know whether this can be possible.if yes,how can i achieve that.

I tried a lot but no luck.:smileycry:

Thanks,

Siva.

[art297]

What determines which newstore gets assigned to which group, which modelid gets assigned to which newstore, which model id gets dropped, and what do you do if there are more than two newstores in a group, or more than three modelids for any newstore?

[Sivanandam]

Hi Art,

Already those process are completed and the input which i gave above are assigned to corresponding group and modelid.

My problem occurs when there are more than one newstore to a group

If therer are two new stores belong to same group then

I need to select two newstores with its  corresponding group but the modelid should not be same.

O/p:

Group          Newstore          Modelid

    10549          6453               5582   

    10549          2001               5217

In the output u can see two newstore belong to the same group but hte modelid are different..This is my requiremtn.

Hope it clarfies.Please let us know if u have any concern.

Yoy can drop any model id there is no criteria for that..