topic PROC SQL QUERY in SAS Programming

PROC SQL QUERY

scb — Thu, 02 Feb 2017 10:38:25 GMT

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;

Re: PROC SQL QUERY

PeterClemmensen — Thu, 02 Feb 2017 10:40:31 GMT

Can you post your log please? 🙂

Re: PROC SQL QUERY

RW9 — Thu, 02 Feb 2017 11:01:31 GMT

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;

Re: PROC SQL QUERY

scb — Thu, 02 Feb 2017 11:10:07 GMT

The answer that I need:

ID Product Amt

123 2 70000

124 2 98700

125 1 343100

Re: PROC SQL QUERY

scb — Thu, 02 Feb 2017 11:11:53 GMT

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

Re: PROC SQL QUERY

RW9 — Thu, 02 Feb 2017 11:32:28 GMT

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;