How to combine dif variable and create new variable with different variable value separate by coma

[dpa]

Hi I have data look like this.

I want like this.

so basically want to combine drug in one variable for each unique id and seq.

Any pointer please ? 

Thanks

[novinosrin · Accepted solution]

data have;
input id seq drug $;
cards;
1 1 a
1 1 b
2 1 d
3 1 b
3 1 c
3 1 d
3 2 e
4 1 a
4 2 h
;

data want;
do until(last.seq);
set have;
by id seq;
length _drug $100;
_drug=catx(',',_drug,drug);
end;
rename _drug=drug;
drop drug;
run;

[dpa]

Thank you it worked but please can you explain logic behind that.

[novinosrin]

Hi @dpa  Sure. The logic is pretty straight forward technique that does row-wise concatentation for each by group. In your case each by-group is a unique combination of Id Seq. Now at this point you'd have already guessed the do until(last.seq) continues to process/execute up until all the records of each by group is read and processed. The concatentation takes place for each record and every record of a by group for the variable drug and finallyy written to the output dataset once the processing is complete. The same is repeated for every such By-group until all records in the input dataset are read and processed. Hope this helps?

[novinosrin]

Perhaps this is easier to understand-


data have;
input id seq drug $;
cards;
1 1 a
1 1 b
2 1 d
3 1 b
3 1 c
3 1 d
3 2 e
4 1 a
4 2 h
;
proc transpose data=have out=temp;
by id seq;
var drug;
run;
data want;
set temp;
drug=catx(',',of col:);
keep id seq drug;
run;

[dpa]

HI Tried to transpose but the only issue to concatenation as after transposing i have so many different variable name for all drugs so can i do that?

[dpa]

Got it now. Thanks

[Patrick]

Something like below should do. Data "have" must be sorted by ID and SEQ.

data want;
  set have;
  by id seq;
  length drug_list $1000;
  drug_list=catx(',',drug_list,drug);
  if last.seq then 
    do;
      output;
      call missing(drug_list);
    end;
  drop drug;
run;

[dpa]

sorry thanks but didn't work

[Patrick]

---

@dpa wrote:
sorry thanks but didn't work

---

Just for the future: It helps all of us if you let us know WHAT didn't work - like some description and/or post the relevant log section which shows the issue.

[dpa]

that's good point. So it didn't give me error but new variable didn't come with drug separated with comma it came as previous drug values. hope that helps.

[Patrick]

@dpa 

Forgot to add a Retain statement so variable drug_list doesn't get re-initialized for every iteration of the data step. Below code that should be o.k.

data want;
  set have;
  by id seq;
  length drug_list $1000;
  retain drug_list;
  drug_list=catx(',',drug_list,drug);
  if last.seq then 
    do;
      output;
      call missing(drug_list);
    end;
  drop drug;
run;

[ballardw]

Just to be consistent about asking, what will you be able to do the combined drug variable that you cannot do with the data in current form?

Almost any any analysis is going to be much harder with multiple values in a single variable.

[dpa]

it's for listing