Help using Base SAS procedures

Rearranging Data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Rearranging Data

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!


Accepted Solutions
Solution
‎04-01-2012 06:16 PM
Respected Advisor
Posts: 3,156

Rearranging Data

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=_Smiley Happy;

merge have1 have2;

by id period;

run;

proc print;run;

Haikuo

View solution in original post


All Replies
PROC Star
Posts: 7,474

Rearranging Data

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 Smiley Surprisedbs

    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;

Solution
‎04-01-2012 06:16 PM
Respected Advisor
Posts: 3,156

Rearranging Data

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=_Smiley Happy;

merge have1 have2;

by id period;

run;

proc print;run;

Haikuo

Respected Advisor
Posts: 4,925

Rearranging Data

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

PG

PG
Super User
Posts: 10,028

Re: Rearranging Data

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

Occasional Contributor
Posts: 10

Rearranging Data

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

New Contributor
Posts: 4

Rearranging Data

WOW..

Smart Answer...

Great...

Super Contributor
Posts: 1,636

Re: Rearranging Data

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

Occasional Contributor
Posts: 10

Rearranging Data

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

Super Contributor
Posts: 1,636

Rearranging Data

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

Respected Advisor
Posts: 4,925

Rearranging Data

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=_Smiley Happy;
var x;
by id period;
id var;
run;

 

PG

PG
Valued Guide
Posts: 765

Re: Rearranging Data

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;

Occasional Contributor
Posts: 10

Rearranging Data

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 570 views
  • 3 likes
  • 8 in conversation