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
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
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
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.