topic Rearranging Data in SAS Procedures

Rearranging Data

Lien728 — Sun, 01 Apr 2012 21:47:39 GMT

Hello all,

Suppose I have a data set that looks like the following:

Id Period Time Conc

1 1 0 0

1 1 5 0

1 1 10 2

1 2 0 0

1 2 5 2

1 2 10 3

2 1 0 0

2 1 5 2

2 1 10 4

2 2 0 0

2 2 5 5

2 2 10 8

I want the output to look like this:

ID Period t1 t2 t3 C1 C2 C3

1 1 0 5 10 0 0 2

1 2 0 5 10 0 2 3

2 1 0 5 10 0 2 4

2 2 0 5 10 0 5 8

I used proc sort by id and period first. Then, I have tried to use an array and first.id/last.id, but seem to be running into problems.

Any ideas?

Thank you!

Rearranging Data

art297 — Sun, 01 Apr 2012 22:14:13 GMT

One method would be that described at: http://support.sas.com/resources/papers/proceedings10/102-2010.pdf

For your data (I can't test this at the moment) it might be:

data have;

input Id Period Time Conc;

cards;

1 1 0 0

1 1 5 0

1 1 10 2

1 2 0 0

1 2 5 2

1 2 10 3

2 1 0 0

2 1 5 2

2 1 10 4

2 2 0 0

2 2 5 5

2 2 10 8

;

proc sql noprint;

select max(obs) into :obs

from ( select count(*) as obs

from have

group by Id Period

)

;

quit;

proc summary nway data=have missing;

class Id Period;

output out = work.want(drop=_type_ _freq_)

idgroup(out[&obs](Time Conc)=);

run;

Rearranging Data

Haikuo — Sun, 01 Apr 2012 22:16:21 GMT

Or do a double transpose and merge:

data have;

input Id$ Period$ Time Conc;

cards;

1 1 0 0

1 1 5 0

1 1 10 2

1 2 0 0

1 2 5 2

1 2 10 3

2 1 0 0

2 1 5 2

2 1 10 4

2 2 0 0

2 2 5 5

2 2 10 8

;

proc sort data=have;

by id period;

run;

proc transpose data=have out=have1 prefix=t;

by id period;

var time;

run;

proc transpose data =have out=have2 prefix=c;

by id period;

var conc;

run;

data want (drop=_:);

merge have1 have2;

by id period;

run;

proc print;run;

Haikuo

Re: Rearranging Data

Linlin — Sun, 01 Apr 2012 22:17:20 GMT

how about:

data have;

input Id Period Time Conc;

cards;

1 1 0 0

1 1 5 0

1 1 10 2

1 2 0 0

1 2 5 2

1 2 10 3

2 1 0 0

2 1 5 2

2 1 10 4

2 2 0 0

2 2 5 5

2 2 10 8

; run;

data want(keep=id period t1-t3 c1-c3);

array t{3} ;

array c{3} ;

count=0;

do until(last.period);

set have;

by id period;

count+1;

t{count}=time;c{count}=conc;

end;

run;

proc print;run;

Obs t1 t2 t3 c1 c2 c3 Id Period

1 0 5 10 0 0 2 1 1

2 0 5 10 0 2 3 1 2

3 0 5 10 0 2 4 2 1

4 0 5 10 0 5 8 2 2

Linlin

Rearranging Data

PGStats — Sun, 01 Apr 2012 22:31:56 GMT

This does it:

data have;
input Id     Period     Time     Conc;
datalines;
1          1               0          0
1          1               5          0
1          1              10         2
1          2               0          0
1          2               5          2
1          2               10          3
2          1               0          0
2          1               5          2
2          1               10          4
2          2               0          0
2          2               5          5
2          2               10          8
;

data have0(keep=id period var x);
i = 0;
do until (last.period);
set have;
by id period;
i + 1;
x = time; var=cats("t",i); output;
x = conc; var=cats("C",i); output;
end;
run;

proc transpose data=have0 out=want(drop=_:);
var x;
by id period;
id var;
run;

Rearranging Data

PGStats — Sun, 01 Apr 2012 22:38:33 GMT

Hai.kuo, this the most elegant solution! Congrats!

Rearranging Data

Lien728 — Mon, 02 Apr 2012 00:38:44 GMT

Thanks, Linlin! I like this method because it uses the array and first/last processes, which is what I had in mind. I'm just wondering though, does variable placement matter in the data set? That is, how can we ensure that Id and period come before t1-t3 and c1-c3? I know how to make sure that the output prints the id and period variables first. Just curious...

Rearranging Data

Lien728 — Mon, 02 Apr 2012 00:39:46 GMT

Thank you so much, Hai.kuo! This does work perfectly.

Rearranging Data

Lien728 — Mon, 02 Apr 2012 00:40:08 GMT

Thanks for all of the help! It was greatly appreciated!

Rearranging Data

Linlin — Mon, 02 Apr 2012 01:38:01 GMT

you can use retain statement to order the variables:

data have;

input Id Period Time Conc;

cards;

1 1 0 0

1 1 5 0

1 1 10 2

1 2 0 0

1 2 5 2

1 2 10 3

2 1 0 0

2 1 5 2

2 1 10 4

2 2 0 0

2 2 5 5

2 2 10 8

; run;

data want(keep=id period t1-t3 c1-c3);

array t{3} ;

array c{3} ;

count=0;

do until(last.period);

set have;

by id period;

count+1;

t{count}=time;c{count}=conc;

end;

run;

data want;

retain id period t1-t3 c1-c3;

set want;

run;

proc print;run;

Obs id period t1 t2 t3 c1 c2 c3

1 1 1 0 5 10 0 0 2

2 1 2 0 5 10 0 2 3

3 2 1 0 5 10 0 2 4

4 2 2 0 5 10 0 5 8

Linlin

Rearranging Data

jontim — Tue, 03 Apr 2012 13:03:05 GMT

WOW..

Smart Answer...

Great...

Re: Rearranging Data

Ksharp — Fri, 06 Apr 2012 04:48:06 GMT

HaiKuo's solution isn't suited for large table . Actually I like Linlin's code more.But She need some more code to get this magic number just like Art did .

data have;
input Id     Period     Time     Conc;
cards;
1          1               0          0
1          1               5          0
1          1              10         2
1          2               0          0
1          2               5          2
1          2               10          3
2          1               0          0
2          1               5          2
2          1               10          4
2          2               0          0
2          2               5          5
2          2               10          8
; run;

 

proc sql noprint;
 select max(count) into : max
  from (select count(*) as count from have group by id,period);
quit;

data want(drop=time conc count);
  array t{&max} ;
  array c{&max} ;
  count=0;
  do until(last.period);
    set have;
    by id period;
    count+1;
    t{count}=time;c{count}=conc;
  end;
run;

Ksharp

Re: Rearranging Data

MikeZdeb — Fri, 06 Apr 2012 15:29:14 GMT

hi ... had a similar idea but used a view since that intermediate data set is just a means to an end (also used two ID variables as mentioned by Ksharp in another discussion) ...

data new / view=new;

set have;

by id period;

count + (-first.period * count) + 1;

var = time; name = 'T'; output;

var = conc; name = 'C'; output;

run;

proc transpose data=new (drop=_name_) out=want;

var var;

by id period;

id name count;

run;