Combine multiple values into one value

[Babloo]

I want to combine multiple values of Entity_ID into one Entity_ID in the new variable Name ENTY_NM.

If I execute the code below I received 5 observations (one Entity_Id for each row ) instead of one Observation.

If I've Entity_Ids like abc_bcf,dfe_efr then I want it to Display like abc_bcf/dfe_efr 

data reqd_vars;
set dis_entity_id;
by ENTITY_ID;
length ENTY_NM $50;
retain ENTY_NM;
if first.ENTITY_ID then ENTY_NM=" ";
ENTY_NM=catx('/',ENTY_NM,ENTITY_ID);
if last.ENTITY_ID then output;
run;

[ballardw]

The First. and Last. are only going help with this if the Entity_id is repeated. If it is not repeated then each entity_id is the First and the Last and would show the behavior you describe.

Example input data.

Example desired output for the given input.

Rules on which ones to combine.

[Babloo]

You're entity_id is not repeated. Could you please tell me how should I
tweak the code now?

[Reeza]

Here are two methods that can be used to do this.

These are full examples and you can run the code directly to see what's happening. 

*create sample data for demonstration;
data have;
    infile cards dlm='09'x;
    input OrgID Product $   States $;
    cards;
1   football    DC
1   football    VA
1   football    MD
2   football    CA
3   football    NV
3   football    CA
;
run;

*Sort - required for both options;
proc sort data=have;
    by orgID;
run;

**********************************************************************;
*Use RETAIN and BY group processing to combine the information;
**********************************************************************;
data want_option1;
    set have;
    by orgID;
    length combined $100.;
    retain combined;

    if first.orgID then
        combined=states;
    else
        combined=catx(', ', combined, states);

    if last.orgID then
        output;
run;

**********************************************************************;
*Transpose it to a wide format and then combine into a single field;
**********************************************************************;
proc transpose data=have out=wide prefix=state_;
    by orgID;
    var states;
run;

data want_option2;
    set wide;
    length combined $100.;
    combined=catx(', ', of state_:);
run;

---

@Babloo wrote:
You're entity_id is not repeated. Could you please tell me how should I
tweak the code now?

---

[Babloo]

If I combine your example with mine then I do not have the variables Orgid
and product.

How will you tweak your example code with only one variable STATE?

[Reeza]

You didn't provide any example so I can't combine anything or say how to modifiy your code. Please provide an example similar to mine, make fake data.

[Babloo]

Well, fake data as follows and both the variables which was in input and
output are character.

Entity_id
123456
78906
236856

Desired result:

Entity_ids
123456/78906/236856

[Reeza · Accepted solution]

If you'd started with this you would have had an answer hours ago 🙂

You only need the By and first if you have multiple entity ids in the table and only want to add one. 

data reqd_vars;
set dis_entity_id end=last_record;

by ENTITY_ID;

length ENTY_NM $50;
retain ENTY_NM;

if first.ENTITY_ID then 
ENTY_NM=catx('/',ENTY_NM,ENTITY_ID);

if last_record then output;

run;

---

@Babloo wrote:
Well, fake data as follows and both the variables which was in input and
output are character.

Entity_id
123456
78906
236856

Desired result:

Entity_ids
123456/78906/236856

---

[ballardw]

---

@Babloo wrote:
Well, fake data as follows and both the variables which was in input and
output are character.

Entity_id
123456
78906
236856

Desired result:

Entity_ids
123456/78906/236856

---

800 posts and not providing data as a data step.