topic Re: help in SAS Programming

help

manjo — Sat, 12 Apr 2014 18:24:44 GMT

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?

Re: help

Ksharp — Sun, 13 Apr 2014 02:38:01 GMT

Post your sample data .

Re: help

manjo — Sun, 13 Apr 2014 07:47:15 GMT

*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

;

Re: help

Ksharp — Sun, 13 Apr 2014 08:57:52 GMT

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

Re: help

manjo — Sun, 13 Apr 2014 14:11:13 GMT

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?

Re: help

Jagadishkatam — Sun, 13 Apr 2014 14:20:10 GMT

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

Thanks,

Jag

Re: help

manjo — Sun, 13 Apr 2014 15:33:31 GMT

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

Re: help

Jagadishkatam — Sun, 13 Apr 2014 16:14:25 GMT

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

Re: help

PGStats — Sun, 13 Apr 2014 18:50:10 GMT

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;

Re: help

manjo — Sun, 13 Apr 2014 21:45:37 GMT

Thank you