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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.