How to display all records captured by count function in sql query

waliaa — Tue, 23 May 2023 05:45:24 GMT

Below is the pass-thru sql query in SAS that I am aiming to modify to display more than two customer records in the same row (by adding additional columns as Name_Example_3, Name_example_4 and so on). The output table below is only displaying two customer records at the moment (using the min and max function). Is there any way I can add more columns if say cus_count >2.

proc sql;

create table test as

select

ID_TYP,

CUST_ID,

count(distinct(a.number)) as cus_count,

count(distinct(a.name)) as Name_Count,

count(distinct(a.DOB)) as DOB_Count,

min(b.NAME) as Name_Example_1,

max(b.NAME) as Name_Example_2,

min(a.number) as cus1,

max(a.number) as cus2,

min(a.ADDED_DT) as cus1_created_date,

max(a.ADDED_DT) as cus2_created_Date,

count(distinct(c.acc_id)) as acc_count

from a left join b

on a.SK = b.sk

left join c

on a.sk_from = c.sk

where

*some condition*

group by CUST_ID_TYP, CUST_ID

having cus_count > 1

quit;

output table below (First row is the header with corresponding values in the second row):

ID_TYP CUST_ID cus_count Name_Count DOB_Count Name_Example_1 Name_Example_2 cus1 cus2 cus1_created_date cus2_created_Date acc_count

NS ABC 2 2 1 Dummy_cus1 Dummy_cus 2 #1 #2 14072022 10022021 4

Re: How to display all records captured by count function in sql query

yabwon — Tue, 23 May 2023 06:31:21 GMT

Don't do it. Bad idea and waist of time.

Instead do it like this:

1) collect SQL data in the "long" form

2) get them to SAS

3) use proc transpose

With this approach you will automatically get data in the "wide" form regardless the number of duplicates.

Bart

topic How to display all records captured by count function in sql query in SAS Programming

How to display all records captured by count function in sql query

Re: How to display all records captured by count function in sql query