DATA Step, Macro, Functions and more

PROC SQL QUERY

Accepted Solution Solved
Reply
Contributor scb
Contributor
Posts: 50
Accepted Solution

PROC SQL QUERY

I have the below dataset and program.  In the test1 output, I only want the first record (min number in product) of every ID.  I have tried using HAVING....but got syntax error.  Anyone can help?

 

 

DATA TEST;
INPUT PRODUCT $ ID $ AMT;
DATALINES;
ABC 123 10000
ABD 123 20000
ABE 123 40000
ABC 124 11300
ABD 124 21800
ABE 124 65600
ABC 125 111300
ABD 125 231800
;
RUN;

 

PROC SQL;
CREATE TABLE TEST1 AS SELECT DISTINCT
ID,
CASE WHEN PRODUCT NOT IN ('ABE') THEN COUNT(DISTINCT PRODUCT)-1 ELSE COUNT(DISTINCT PRODUCT) END AS PRODUCTS,
SUM(AMT) AS AMT
FROM TEST
GROUP BY ID
ORDER BY ID;
QUIT;


Accepted Solutions
Solution
‎02-02-2017 09:29 AM
Super User
Super User
Posts: 7,401

Re: PROC SQL QUERY

Thats not what you posted in your original question:

"I only want the first record (min number in product) of every ID."

Hence why I wasted my time coding it like that.

 

Its not clear to my the logic for product count, why does the last id have 0 when there are 2 products not ABE?

data test;
  input product $ id $ amt;
datalines;
ABC 123 10000
ABD 123 20000
ABE 123 40000
ABC 124 11300
ABD 124 21800
ABE 124 65600
ABC 125 111300
ABD 125 231800
;
run;

proc sql;
  create table WANT as
  select  ID,
          sum(case when PRODUCT="ABE" then 0 else 1 end)  as PRODUCT,
          sum(AMT) as AMT
  from    TEST
  group by ID;
quit;

 

View solution in original post


All Replies
PROC Star
Posts: 551

Re: PROC SQL QUERY

Can you post your log please? Smiley Happy

Contributor scb
Contributor
Posts: 50

Re: PROC SQL QUERY

PROC SQL;
740 CREATE TABLE TEST1 AS SELECT DISTINCT
741 ID,
742 CASE WHEN PRODUCT NOT IN ('ABE') THEN COUNT(DISTINCT PRODUCT)-1 ELSE COUNT(DISTINCT
742! PRODUCT) END AS PRODUCTS,
743 SUM(AMT) AS AMT
744 FROM TEST
745 GROUP BY ID
746 HAVING PRODUCTS IN (SELECT MIN(PRODUCT) FROM TEST WHERE PRODUCT NOT IN 'ABE');
-----
22
76
ERROR 22-322: Syntax error, expecting one of the following: (, SELECT.

ERROR 76-322: Syntax error, statement will be ignored.

747 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

Super User
Super User
Posts: 7,401

Re: PROC SQL QUERY

As for SQL:

proc sql;
  create table WANT as
  select  *
  from    TEST
  group by ID
  having  AMT=min(AMT);
quit;

Is there a need to do it with SQL, looks like a simple sort take first observation to me:

data test;
  input product $ id $ amt;
datalines;
ABC 123 10000
ABD 123 20000
ABE 123 40000
ABC 124 11300
ABD 124 21800
ABE 124 65600
ABC 125 111300
ABD 125 231800
;
run;

proc sort data=test;
  by id product amt;
run;

data want;
  set test;
  by id;
  if first.id;
run;
Contributor scb
Contributor
Posts: 50

Re: PROC SQL QUERY

The answer that I need:

 

ID   Product    Amt

123  2            70000

124  2            98700

125  1            343100

Solution
‎02-02-2017 09:29 AM
Super User
Super User
Posts: 7,401

Re: PROC SQL QUERY

Thats not what you posted in your original question:

"I only want the first record (min number in product) of every ID."

Hence why I wasted my time coding it like that.

 

Its not clear to my the logic for product count, why does the last id have 0 when there are 2 products not ABE?

data test;
  input product $ id $ amt;
datalines;
ABC 123 10000
ABD 123 20000
ABE 123 40000
ABC 124 11300
ABD 124 21800
ABE 124 65600
ABC 125 111300
ABD 125 231800
;
run;

proc sql;
  create table WANT as
  select  ID,
          sum(case when PRODUCT="ABE" then 0 else 1 end)  as PRODUCT,
          sum(AMT) as AMT
  from    TEST
  group by ID;
quit;

 

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 160 views
  • 0 likes
  • 3 in conversation