topic Re: Create variables each contains ordered values from existing variable within by group in SAS Programming

Create variables each contains ordered values from existing variable within by group

zzfsimona — Tue, 03 Jul 2018 07:33:02 GMT

Hi all,

I have a data set that contains such variables:

id	time	drug
1	2/1/2010	A
1	3/1/2010	B
1	4/1/2010	B
1	5/1/2010	C
2	3/2/2010	B
2	4/2/2010	C
3	5/4/2010	A
3	6/4/2010	A
3	7/4/2010	C

I wanted to create variables of drug1 drug2 drug3 drug4...etc which stores values of variable drug in a chronological order within per by group, so the output looks like:

id	time	drug	drug1	drug2	drug3
1	2/1/2010	A	A	B	C
1	3/1/2010	B	A	B	C
1	4/1/2010	B	A	B	C
1	5/1/2010	C	A	B	C
2	3/2/2010	B	B	C	.
2	4/2/2010	C	B	C	.
3	5/4/2010	A	A	C	.
3	6/4/2010	A	A	C	.
3	7/4/2010	C	A	C	.

I can only think of the "sort, retain, carry down non-missing value" method. Is there a more efficient way to do this?

Thank you!

Re: Create variables each contains ordered values from existing variable within by group

RW9 — Tue, 03 Jul 2018 07:51:20 GMT

Sorry your going to have to explain that a bit clearer. In the presented test data (which is not in a datastep!!!), there are four rows for id 1, are you saying that you want the sequence from all the id sorted, transposed and then merged back to the original data? If so then - and note this is not tested as no test data in a datastep provided:

proc sort data=have out=list nodupkey;
  by id drug;
run;
proc tranpose data=list out=llist;
  by id;
  var drug;
run;
data want;
  merge have llist;
  by id;
run;

Re: Create variables each contains ordered values from existing variable within by group

Astounding — Tue, 03 Jul 2018 11:47:06 GMT

I think PROC TRANSPOSE is the right general direction, but I would make a few changes. First, since you indicated chronological order, the sorting order would be:

proc sort data=have;

by id time;

run;

Note that this assumes your TIME values are true SAS dates, not text.

Then get the variable names you would like:

proc transpose data=have prefix=drug out=druglist (drop=_name_);

by id;

var drug;

run;

Finally, merge as was suggested:

data want;

merge have druglist;

by id;

run;

Re: Create variables each contains ordered values from existing variable within by group

zzfsimona — Wed, 04 Jul 2018 03:58:53 GMT

Thank you for the suggestion. What if I wanted the DISTINCT values transposed?

For example for id=1, now they have

drug1 drug2 drug3 drug4

A B B C

I want the distinct values so:

drug1 drug2 drug3

A B C

Thanks!

Re: Create variables each contains ordered values from existing variable within by group

andreas_lds — Wed, 04 Jul 2018 05:52:12 GMT

Untested: use proc sort with nodupkey option before transposing:

proc sort data=have out=singles nodupkey;
  by id drug;
run;

Wait .... that doesn't work because you need the dates of the eliminated duplicates.

Re: Create variables each contains ordered values from existing variable within by group

RW9 — Wed, 04 Jul 2018 07:35:10 GMT

Did my post not do this for you?

Re: Create variables each contains ordered values from existing variable within by group

Astounding — Wed, 04 Jul 2018 16:51:31 GMT

Then you need to further define the outcome. You asked for chronological order. What if the chronological order is ABCB. Do you want ABC as the result or ACB as the result? That's just a simple example. You really need a couple of comprehensive rules about what the order should be.