SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
VALLY
Fluorite | Level 6

Good day community .

i want to concatenate contents from the same column. 

dataset (test) looks like:

Customer_Num Product_Category_Desc

7293MUTUAL FUND INVESTMENTS
7293NOTICE INVESTMENTS
7293SERVICE PRODUCT
7293UNCATEGORISED
53916CARD BASED SAVINGS
53916DEMAND DEPOSITS
53916DEMAND INVESTMENTS
53916FIXED INVESTMENTS
53916SERVICE PRODUCT

 

The output table (combined) will be:

Customer_Num prod_id_combined

7293MUTUAL FUND INVESTMENTS, NOTICE INVESTMENTS, SERVICE PRODUCT, UNCATEGORISED
53916CARD BASED SAVINGS, DEMAND DEPOSITS, DEMAND INVESTMENTS, FIXED INVESTMENTS, SERVICE PRODUCT
code to produce the expected outcome.

proc sql;
   create table combined as 
   select Customer_Num, 
          catx(', ', Product_Category_Desc) as prod_id_combined
   from (select distinct Customer_Num, Product_Category_Desc 
         from test)
   group by Customer_Num;
quit;
1 ACCEPTED SOLUTION

Accepted Solutions
quickbluefish
Lapis Lazuli | Level 10

Try this:

proc sort data=have; by customer_num product_category_desc; run;

data want;
set have;
by customer_num product_category_desc;
length all_descrip $1000;
array T {100} $150 _temporary_;
if first.customer_num then do;
    call missing(of T[*]);
    recnum=0;
end;
if first.product_category_desc then do;
    recnum+1;
    T[recnum]=product_category_desc;
end;
if last.customer_num then all_descrip=catx(',', of T[*]);
keep customer_num all_descrip;
run;

If any customer has more than 100 unique products, then update the {100} part above to whatever that maximum number is.  And if any of the product descriptions are more than 150 characters in length, update the $150 to that maximum length.  Otherwise, you can safely leave as-is.  

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

May I ask why you need to do this? What is wrong with leaving the data as it is and working with it in the long arrangement?

--
Paige Miller
VALLY
Fluorite | Level 6

i want to have a view of a distinct customer based on all the products in one columns , i hope i have answered your question 

PaigeMiller
Diamond | Level 26

Is that it, you just all the text concatenated for a customer, and that's the end of the project? Or are you planning to do analysis once you have that? If so, what types of analysis? 

--
Paige Miller
quickbluefish
Lapis Lazuli | Level 10

As @PaigeMiller seems to be saying, though, the result of this is likely to be hard to use for any analysis.  If you really want to collapse this to one row per person, then I would suggest you start by making a variable for each of your unique product descriptions (e.g., make variables like "mutual_fund_investments", etc.), then populate each of those variables with a 0 or 1 depending on whether the customer had that product.  

quickbluefish
Lapis Lazuli | Level 10

Try this:

proc sort data=have; by customer_num product_category_desc; run;

data want;
set have;
by customer_num product_category_desc;
length all_descrip $1000;
array T {100} $150 _temporary_;
if first.customer_num then do;
    call missing(of T[*]);
    recnum=0;
end;
if first.product_category_desc then do;
    recnum+1;
    T[recnum]=product_category_desc;
end;
if last.customer_num then all_descrip=catx(',', of T[*]);
keep customer_num all_descrip;
run;

If any customer has more than 100 unique products, then update the {100} part above to whatever that maximum number is.  And if any of the product descriptions are more than 150 characters in length, update the $150 to that maximum length.  Otherwise, you can safely leave as-is.  

sas-innovate-white.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9.

 

Early bird rate extended! Save $200 when you sign up by March 31.

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.

SAS Training: Just a Click Away

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

Browse our catalog!

Discussion stats
  • 5 replies
  • 502 views
  • 2 likes
  • 3 in conversation