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!
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
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;
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
Hai.kuo, this the most elegant solution! Congrats!
PG
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
Thank you so much, Hai.kuo! This does work perfectly.
WOW..
Smart Answer...
Great...
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
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...
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
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
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;
Thanks for all of the help! It was greatly appreciated!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.