topic Re: Combine multiple values into one value in SAS Programming

Combine multiple values into one value

Babloo — Mon, 09 Sep 2019 13:55:33 GMT

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;

Re: Combine multiple values into one value

ballardw — Mon, 09 Sep 2019 14:48:44 GMT

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.

Re: Combine multiple values into one value

Babloo — Mon, 09 Sep 2019 15:08:44 GMT

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

Re: Combine multiple values into one value

Reeza — Mon, 09 Sep 2019 15:15:42 GMT

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?

Re: Combine multiple values into one value

Babloo — Mon, 09 Sep 2019 16:13:44 GMT

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?

Re: Combine multiple values into one value

Reeza — Mon, 09 Sep 2019 16:30:25 GMT

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.

Re: Combine multiple values into one value

Babloo — Mon, 09 Sep 2019 16:36:44 GMT

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

Re: Combine multiple values into one value

Reeza — Mon, 09 Sep 2019 16:41:12 GMT

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

Re: Combine multiple values into one value

ballardw — Mon, 09 Sep 2019 17:13:48 GMT

@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.