topic Re: How to combine dif variable and create new variable with different variable value separate by co in SAS Programming

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

dpa — Thu, 11 Jun 2020 21:11:40 GMT

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

Re: How to combine dif variable and create new variable with different variable value separate by co

novinosrin — Thu, 11 Jun 2020 21:28:27 GMT

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;

Re: How to combine dif variable and create new variable with different variable value separate by co

novinosrin — Thu, 11 Jun 2020 21:34:05 GMT

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;

Re: How to combine dif variable and create new variable with different variable value separate by co

Patrick — Sat, 13 Jun 2020 00:03:05 GMT

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;

Re: How to combine dif variable and create new variable with different variable value separate by co

ballardw — Thu, 11 Jun 2020 22:15:06 GMT

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.

Re: How to combine dif variable and create new variable with different variable value separate by co

dpa — Thu, 11 Jun 2020 22:32:44 GMT

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?

Re: How to combine dif variable and create new variable with different variable value separate by co

dpa — Thu, 11 Jun 2020 22:33:03 GMT

sorry thanks but didn't work

Re: How to combine dif variable and create new variable with different variable value separate by co

dpa — Thu, 11 Jun 2020 22:33:31 GMT

it's for listing

Re: How to combine dif variable and create new variable with different variable value separate by co

dpa — Thu, 11 Jun 2020 22:39:41 GMT

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

Re: How to combine dif variable and create new variable with different variable value separate by co

novinosrin — Fri, 12 Jun 2020 11:50:59 GMT

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?

Re: How to combine dif variable and create new variable with different variable value separate by co

Patrick — Fri, 12 Jun 2020 06:13:39 GMT

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

Re: How to combine dif variable and create new variable with different variable value separate by co

dpa — Fri, 12 Jun 2020 13:16:54 GMT

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.

Re: How to combine dif variable and create new variable with different variable value separate by co

dpa — Fri, 12 Jun 2020 13:22:18 GMT

Got it now. Thanks

Re: How to combine dif variable and create new variable with different variable value separate by co

Patrick — Sat, 13 Jun 2020 00:05:34 GMT

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