BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
manjo
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

9 REPLIES 9
Ksharp
Super User

Post your sample data .

manjo
Calcite | Level 5

*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
Super User
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
Calcite | Level 5

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
Amethyst | Level 16

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

Thanks,

Jag

Thanks,
Jag
manjo
Calcite | Level 5

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
Amethyst | Level 16

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
PGStats
Opal | Level 21

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
manjo
Calcite | Level 5

Thank you

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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