Solved: Rearranging Data

Lien728 · Posted 04-01-2012 05:47 PM

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!

Haikuo · Posted 04-01-2012 06:16 PM

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

View solution in original post

art297 · Posted 04-01-2012 06:14 PM

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;

Haikuo · Posted 04-01-2012 06:16 PM

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

PGStats · Posted 04-01-2012 06:38 PM

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

PG

Ksharp · Posted 04-06-2012 12:48 AM

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

Lien728 · Posted 04-01-2012 08:39 PM

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

jontim · Posted 04-03-2012 09:03 AM

WOW..

Smart Answer...

Great...

Linlin · Posted 04-01-2012 06:17 PM

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

Lien728 · Posted 04-01-2012 08:38 PM

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

Linlin · Posted 04-01-2012 09:38 PM

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

PGStats · Posted 04-01-2012 06:31 PM

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;

PG

MikeZdeb · Posted 04-06-2012 11:29 AM

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;

Lien728 · Posted 04-01-2012 08:40 PM

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

Rearranging Data

Rearranging Data

Rearranging Data

Rearranging Data

Rearranging Data

Re: Rearranging Data

Rearranging Data

Rearranging Data

Re: Rearranging Data

Rearranging Data

Rearranging Data

Rearranging Data

Re: Rearranging Data

Rearranging Data

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away