help

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

help

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?


Accepted Solutions
Solution
‎04-13-2014 04:57 AM
Super User
Posts: 9,671

Re: help

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

View solution in original post


All Replies
Super User
Posts: 9,671

Re: help

Post your sample data .

Occasional Contributor
Posts: 7

Re: help

*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

;

Solution
‎04-13-2014 04:57 AM
Super User
Posts: 9,671

Re: help

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

Occasional Contributor
Posts: 7

Re: help

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?

Trusted Advisor
Posts: 1,128

Re: help

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

Thanks,

Jag

Thanks,
Jag
Occasional Contributor
Posts: 7

Re: help

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

Trusted Advisor
Posts: 1,128

Re: help

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

Thanks,
Jag
Respected Advisor
Posts: 4,641

Re: help

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

PG
Occasional Contributor
Posts: 7

Re: help

Thank you

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 367 views
  • 10 likes
  • 4 in conversation