topic Fetching records with unique value in SAS Procedures

Fetching records with unique value

Sivanandam — Fri, 29 Jul 2011 04:47:51 GMT

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.

Fetching records with unique value

Sivanandam — Fri, 29 Jul 2011 04:50:30 GMT

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 2205

2648 3302

2648 2648

2648 5217

2648 5273

6453 5582

6453 3251

6453 2492

6453 5273

6453 2492

6453 2205

Fetching records with unique value

ieva — Fri, 29 Jul 2011 05:30:42 GMT

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;

Fetching records with unique value

Sivanandam — Fri, 29 Jul 2011 09:30:22 GMT

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

Re: Fetching records with unique value

ballardw — Fri, 29 Jul 2011 21:02:05 GMT

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;

Re: Fetching records with unique value

Ksharp — Thu, 04 Aug 2011 09:23:07 GMT

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

Fetching records with unique value

Sivanandam — Thu, 04 Aug 2011 15:08:01 GMT

Hi Sharp,

i need th e output as follows

store store1

2648 5582

6453 3251

Thanks,
Siva

Fetching records with unique value

LinusH — Fri, 05 Aug 2011 11:28:32 GMT

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

Fetching records with unique value

Ksharp — Tue, 09 Aug 2011 10:31:18 GMT

Not quit sure what you want.

Do ballardw and Linus 's code match your demand?

Fetching records with unique value

Sivanandam — Fri, 19 Aug 2011 12:33:11 GMT

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

Fetching records with unique value

Sivanandam — Fri, 19 Aug 2011 12:33:52 GMT

yes sharp..Till now i didn't get

Re: Fetching records with unique value

Ksharp — Mon, 22 Aug 2011 02:35:06 GMT

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

Fetching records with unique value

Sivanandam — Sat, 10 Sep 2011 03:50:07 GMT

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.

Fetching records with unique value

art297 — Sat, 10 Sep 2011 04:04:38 GMT

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?

Re: Fetching records with unique value

Sivanandam — Sat, 10 Sep 2011 04:21:14 GMT

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..

Re: Fetching records with unique value

jonam — Sat, 10 Sep 2011 08:13:03 GMT

Hi siva,

Based on your requirement i wrote the following code; I've assumed you've one (group, newstore) as key , another (group, modelid). Is there any chance that you'll have same model id in different groups? For the current scenario i kept the array limit as 3. but you can change that based on your need.

data g_s_m;

input Group Newstore Modelid;

datalines;

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

;

/* source 1 */

Proc sql;

create table temp as

select

g_s.group, g_s.newstore,g_m.modelid

from

(Select distinct

group

, newstore

from g_s_m

) g_s,

(select distinct

group

, modelid

from g_s_m) as g_m

where g_s.group=g_m.group

order by group, newstore, modelid;

quit;

proc print;run;

/*I realised, For the subset of data provided creation of source1 produced exactly the same result as g_s_m */ . So instead of that directly we can use the g_s_m table */

/* using source1 to create source2 with unique modelid at group level */

proc sql;

create table group_modelid as

select distinct group, modelid from

temp

order by group, modelid desc

;

quit;

/* transpose modelid at group */

proc transpose data=group_modelid out =grp_col_mod prefix=model_;

by group;

var modelid;

run;

proc sql;

create table input1

select distinct group, newstore

from temp;

quit;

data final;

merge input1 (in=a) grp_col_mod (in=b);

by group;

if a and b;

run;

data final1(keep=group newstore final_mod);

set final;

by group;

array model_sel model_1-model_3;

if first.group then index=1;

else index+1;

final_mod=model_sel{index};

run;

proc print;

run;

Message was edited by: Jeyvinth Manoj Rayan

Fetching records with unique value

NN — Sat, 10 Sep 2011 08:49:21 GMT

Hi,

Not sure but will this work.

data a;

infile datalines delimiter='|';

input a : $10. b : $10. c : $10.;

datalines;

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

;

run;

proc sort data=a;

by descending a descending b descending c;

run;

data b;

set a;

by descending a descending b descending c;

retain _b _c;

format _b _c $100.;

if first.a then do;

_b=b;

_c=c;

flag=1;

end;

else do;

if findw(_b,b,',','T')=0 then do;

if findw(_c,c,',','T') = 0 then do;

flag=1;

_c=cats(_c,',', c);

_b=cats(_b,',', b);

end;

if flag=1 then output;

drop _b _c;

run;

Fetching records with unique value

Sivanandam — Sat, 10 Sep 2011 15:00:54 GMT

Hi,

i ran the sample code which you gave it was as i expected ..

Could you please explain the below sample code

data final1(keep=group newstore final_mod);

set final;

by group;

array model_sel model_1-model_3;

if first.group then index=1;

else index+1;

final_mod=model_sel{index};//What this line will do

run;

proc print;

run;

Fetching records with unique value

art297 — Sat, 10 Sep 2011 15:46:12 GMT

Sivanandam,

I didn't write the code, but can definitely explain it.

The file, final, apparently has your values in multiple records in the order that you want them, in variables model_1 to model_3, with the desired value for the first record (in each group) in model_1, the second record in Model_2, etc.

The code keeps track of which record it is looking at by using

if first.group then index=1;

else index+1;

Then, it selects final_mod by picking the first, second or third value, respectively:

final_mod=model_sel{index}; /*<-What this line will do*/

Fetching records with unique value

Tom — Sat, 10 Sep 2011 18:19:30 GMT

Your original data seems to have all new stores matched to all model ids.

If this is true then you can use a data step many-to-many merge on GROUP to assign stores to models.

data have ;

input Group Newstore Modelid ;

cards;

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

run;

proc sort data=have(keep=group modelid) out=modelids nodupkey;

by group modelid;

run;

proc sort data=have(keep=group newstore) out=stores nodupkey;

by group newstore;

run;

* Need to reset the IN flag to keep it from matching the last store to all of the remaining models ;

data want ;

in1=0;

merge stores(in=in1) modelids;

by group ;

if in1;

put group newstore modelid ;

run;

If you want to introduce some randomness into the issue then sort the stores and models randomly within the groups before the merge.