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.
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
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;
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
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;
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
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;
/LinusWhether 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
Hi Sharp,
i need th e output as follows
store store1
2648 5582
6453 3251
Thanks,
Siva
Not quit sure what you want.
Do ballardw and Linus 's code match your demand?
yes sharp..Till now i didn't get
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
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.
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?
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..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.