PROC SQL QUERY

[scb]

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;

[PeterClemmensen]

Can you post your log please? 🙂

[scb]

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

[RW9]

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;

[scb]

The answer that I need:

ID   Product    Amt

123  2            70000

124  2            98700

125  1            343100

[RW9 · Accepted solution]

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;