topic Re: merge two rows data in SAS Programming

merge two rows data

radha009 — Tue, 28 May 2019 19:53:26 GMT

i have data like below

request parent number

123 234 789

123 234 545

want to merge like

request parent number

123 234 789/545

how to code this?

Re: merge two rows data

PaigeMiller — Tue, 28 May 2019 20:03:50 GMT

I always get the feeling that the generalizations I make when the problem is shown with a very small number of records (in this case 2 records) will not hold for larger data sets. So it would certainly help if you provided a larger data set with more conditions. Nevertheless, what you ask for is provided as follows:

data a;
	input request parent number;
	cards;
	123 234 789
	123 234 545
;
proc transpose data=a out=b;
    by request parent;
run;
data want;
    set b;
	number = catx('/',col1,col2);
	drop _name_ col1 col2;
Run;

Re: merge two rows data

Reeza — Tue, 28 May 2019 20:56:01 GMT

Here are the two most common methods:

*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;

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

@radha009 wrote:

i have data like below

request parent number

123 234 789

123 234 545

want to merge like

request parent number

123 234 789/545

how to code this?

Re: merge two rows data

LFern — Wed, 16 Oct 2019 14:01:06 GMT

This was super useful, thank you!