BookmarkSubscribeRSS Feed
waliaa
Fluorite | Level 6

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

1 REPLY 1
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 835 views
  • 0 likes
  • 2 in conversation