BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lien728
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

12 REPLIES 12
art297
Opal | Level 21

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
Onyx | Level 15

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
Opal | Level 21

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

PG

PG
Ksharp
Super User

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
Calcite | Level 5

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

jontim
Calcite | Level 5

WOW..

Smart Answer...

Great...

Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Opal | Level 21

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

PG
MikeZdeb
Rhodochrosite | Level 12

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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