topic Re: Concatenate values in SAS Programming

Concatenate values

Lokesh4 — Fri, 12 Jun 2020 11:17:11 GMT

Team could you please help me out with the below problem

Eg: I have the below table

Company ID DESIGNATION

ABC 1 Employee

ABC 1 Supervisor

ABC 2 Manager

I wanted the data to be concatenated as below in one variable, the new_var should have id concatenated with designation, can you please help me out

Company ID DESIGNATION New_var

ABC 1 Employee 1-Employee, 1-Supervisor, 2-Manager

Re: How to create a data step version of your data AKA generate sample data for forums

ChrisNZ — Fri, 12 Jun 2020 11:16:41 GMT

@Lokesh4

Welcome to the SAS community!

Why on earth did you post a new question after an unrelated discussion?

As importantly, did you even read what the discussion is about?

It is about posting your sample data in the correct format, which you -very ironically- have not done!

Moving the question from here to the programming forum.

Re: Concatenate values

PaigeMiller — Fri, 12 Jun 2020 11:27:24 GMT

@Lokesh4 wrote:

Team could you please help me out with the below problem

Eg: I have the below table

Company ID DESIGNATION

ABC 1 Employee

ABC 1 Supervisor

ABC 2 Manager

I wanted the data to be concatenated as below in one variable, the new_var should have id concatenated with designation, can you please help me out

Company ID DESIGNATION New_var

ABC 1 Employee 1-Employee, 1-Supervisor, 2-Manager

The output you want is not consistent with the Input. ID 1 has only two records in the input, yet in the output it seems as if you are concatenating three records for ID 1. Could you please clarify this? Also, where does DESIGNATION come from?

Also, concatenating long data sets into wide data as you are asking to do is rarely necessary and rarely a good thing to do. What will you be doing with this variable NEW_VAR?

Re: Concatenate values

Kurt_Bremser — Fri, 12 Jun 2020 11:27:55 GMT

If a company has 3000 employees or more, you will overflow the maximum length of character variables, unless your rule for concatenating is different than what it seems to be in your example.

Re: Concatenate values

CarlosSpranger — Fri, 12 Jun 2020 14:50:25 GMT

Hello @Lokesh4,

Whatever your need is, here you have it:

data test;
input Company $ ID Designation $;
Datalines;
ABC 1 Employee
ABC 1 Supervisor
ABC 2 Manager
run;

Data want;
set test;
new_var= CATX('-',ID,Designation);
run;

/*The result below*/