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



sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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