BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

If there are multiple records per id, how to make it as one record as below:

 

id    brand    
123   iphone
234   samsung
234   iphone


id  brand
123 iphone
234 samsung | iphone

3 REPLIES 3
Reeza
Super User

Here's two different ways:

 

*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;
kiranv_
Rhodochrosite | Level 12

there are many similar answers in this communities. one is below link.

 

https://communities.sas.com/t5/Base-SAS-Programming/Concatenate-multiple-rows-into-a-single-value/td...

SASUser_22
Calcite | Level 5

You can use first.id for top most record or last.id for last record.

 

doing proc sort before this step will give better results.

sas-innovate-wordmark-2025-midnight.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. Sign up by March 14 for just $795.


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
  • 3 replies
  • 1150 views
  • 2 likes
  • 4 in conversation