help

[manjo]

Hi Thanks all in advance for your answers

My Question

I have 2 tables, a person table containing 1 record per person, and an orders table with many orders per person, with a key on person_id.

Using proc sql, how would you create a table containing only the id and name of any people that have made two or more orders?

[Ksharp]

Post your sample data .

[manjo]

*order table;

data abc1;

input eid name$ age order_nu;

cards;

100 ppp 26 112

100 ppp 26 113

100 ppp 25 116

101 qqq 32 114

102 qqw 31 119

102 qqq 32 120

104 yyy 53 121

102 xxx 51 121

;

*person table;

data abc;

input eid name$ age;

cards;

100 ppp 26

101 qqq 32

102 qqw 31

104 yyy 53

;

[Ksharp · Accepted solution]

data abc1;
input eid name$ age order_nu;
cards;
100 ppp 26 112
100 ppp 26 113
100 ppp 25 116
101 qqq 32 114
102 qqw 31 119
102 qqq 32 120
104 yyy 53 121
102 xxx 51 121
;
 

data abc;
input eid name$ age;
cards;
100 ppp 26
101 qqq 32
102 qqw 31
104 yyy 53
;
run;

proc sql;
 create table want as
 select * from abc 
   where eid in (select distinct eid from abc1 group by eid having count(*) ge 2) ;
quit;

/****** OR *******/

proc sql;
 create table want1 as
  select * from abc 
   where exists(select eid from abc1 where eid=abc.eid group by eid having count(*) ge 2) ;
quit;

Xia Keshan

[manjo]

Thank you Ksharp

If in case i want to create a table containing only the id and name of any people and THEIR ORDERS who have made two or more orders means how can i alter this?

[Jagadishkatam]

In that case, you can just replace the select * with select eid, name. ' * ' indicates selecting all the variables for display.

Thanks,

Jag

[manjo]

If i run that above codes the output is this

eid     name     age     order_nu

100     ppp     26     112

102     qqw     31     119

but instead of that i need an out put of

eid name age order_nu

100 ppp 26 112

100 ppp 26 113

100 ppp 25 116

102 qqw 31 119

102 qqq 32 120

102 xxx 51 121

like this

all the records expect the person who made order only once

[Jagadishkatam]

Please try the below code

proc sql;

create table want as

select * from abc1

   where eid in (select distinct eid from abc1 group by eid having count(*) gt 1) ;

quit;

Thanks,

Jag

[PGStats]

Names are incoherent between tables abc1 and abc for eid=102. Every single piece of information should belong to a single table. Assuming eid is the most valid key, you should do:

data orders;

input eid name$ age order_nu;

drop age name;

cards;

100 ppp 26 112

100 ppp 26 113

100 ppp 25 116

101 qqq 32 114

102 qqw 31 119

102 qqq 32 120

104 yyy 53 121

102 xxx 51 121

;

data persons;

input eid name$ age;

cards;

100 ppp 26

101 qqq 32

102 qqw 31

104 yyy 53

;

proc sql;

create table multipleOrders as

select p.eid, p.name, count(o.order_nu) as nbOrders

from

     persons as p inner join

     orders as o on p.eid=o.eid

group by p.eid, p.name

having count(order_nu) > 1;

select * from multipleOrders

quit;

PG

[manjo]

Thank you