Proc SQL help needed please.
in [SAS]
|
From: zoooom on 24 Jan 2010 06:28 I am attempting to create a new table using proc sql that takes all the records from test_table and inner join it with items table on cust_id. What I want to end up with with is a table that contains 1 record for each cust_id, and the flag_id associated with the min(order_date) returned after the order_date of 10/08/09. I have tried various iterations of code and not been able to solve this problem - a slow death by syntax followed. In the end I solved it using the below code, but is there a more efficient way to do this in one proc sql block using a subquery? All replies greatly appreciated. proc sql; create table table_a as select a.*, b.order_date, b.flag_id from test_table a, items b where a.cust_id = b.cust_id and b.order_date>= '10aug2009'd order by cust_id, order_date ; data table_a; set table_a; by cust_id; if first.cust_id; run;
From: Arthur Tabachneck on 24 Jan 2010 08:45 You didn't provide any test data and desired results, thus I can only guess whether the following is doing what you expect: proc sql; create table table_a as select distinct a.*, min(b.order_date), b.flag_id from test_table a, items b where a.cust_id = b.cust_id and b.order_date>= '10aug2009'd group by a.cust_id ; quit; HTH, Art ---------------- On Jan 24, 6:28 am, "zoooom" <zoo...(a)yahoo.co.uk> wrote: > I am attempting to create a new table using proc sql that takes all the > records from test_table and inner join it with items table on cust_id. > > What I want to end up with with is a table that contains 1 record for each > cust_id, and the flag_id associated with the min(order_date) returned after > the order_date of 10/08/09. > > I have tried various iterations of code and not been able to solve this > problem - a slow death by syntax followed. > > In the end I solved it using the below code, but is there a more efficient > way to do this in one proc sql block using a subquery? > > All replies greatly appreciated. > > proc sql; > create table table_a > as select a.*, b.order_date, b.flag_id > from test_table a, items b > where a.cust_id = b.cust_id > and b.order_date>= '10aug2009'd > order by cust_id, order_date > ; > > data table_a; > set table_a; > > by cust_id; > if first.cust_id; > run;
From: Sigurd Hermansen on 25 Jan 2010 11:52 The SQL solution that Art posted will come very close to the results of the SAS SQL/Data step solution that you found. Art's solution will leave more than one order per customer in the yield of the query when any one customer has more than one order per day in the database and that order has a different flag value. As I said often, the arbitrary nature of the first.x or SORT DEDUPKEY method of deduplication leads to uncomfortable compromises. You won't find an SQL equivalent of the first.x method. SQL, to its credit, doesn't arbitrarily discard information. S -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of zoooom Sent: Sunday, January 24, 2010 6:29 AM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Proc SQL help needed please. I am attempting to create a new table using proc sql that takes all the records from test_table and inner join it with items table on cust_id. What I want to end up with with is a table that contains 1 record for each cust_id, and the flag_id associated with the min(order_date) returned after the order_date of 10/08/09. I have tried various iterations of code and not been able to solve this problem - a slow death by syntax followed. In the end I solved it using the below code, but is there a more efficient way to do this in one proc sql block using a subquery? All replies greatly appreciated. proc sql; create table table_a as select a.*, b.order_date, b.flag_id from test_table a, items b where a.cust_id = b.cust_id and b.order_date>= '10aug2009'd order by cust_id, order_date ; data table_a; set table_a; by cust_id; if first.cust_id; run;
From: zoooom on 25 Jan 2010 12:47 that's a really helpful answer, thanks a lot. "Sigurd Hermansen" <HERMANS1(a)WESTAT.COM> wrote in message news:FE10F31634E7F34B87AA143D59608541405C3B84(a)EX-CMS01.westat.com... > The SQL solution that Art posted will come very close to the results of > the SAS SQL/Data step solution that you found. Art's solution will leave > more than one order per customer in the yield of the query when any one > customer has more than one order per day in the database and that order > has a different flag value. > > As I said often, the arbitrary nature of the first.x or SORT DEDUPKEY > method of deduplication leads to uncomfortable compromises. You won't find > an SQL equivalent of the first.x method. SQL, to its credit, doesn't > arbitrarily discard information. > S > > -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of > zoooom > Sent: Sunday, January 24, 2010 6:29 AM > To: SAS-L(a)LISTSERV.UGA.EDU > Subject: Proc SQL help needed please. > > I am attempting to create a new table using proc sql that takes all the > records from test_table and inner join it with items table on cust_id. > > What I want to end up with with is a table that contains 1 record for each > cust_id, and the flag_id associated with the min(order_date) returned > after > the order_date of 10/08/09. > > I have tried various iterations of code and not been able to solve this > problem - a slow death by syntax followed. > > In the end I solved it using the below code, but is there a more efficient > way to do this in one proc sql block using a subquery? > > All replies greatly appreciated. > > proc sql; > create table table_a > as select a.*, b.order_date, b.flag_id > from test_table a, items b > where a.cust_id = b.cust_id > and b.order_date>= '10aug2009'd > order by cust_id, order_date > ; > > data table_a; > set table_a; > > by cust_id; > if first.cust_id; > run;
|
Pages: 1 Prev: Multiple data sets based on a variable Next: need info regarding forecasting presentation |