Transpose?

Solved
Super Contributor
Posts: 1,041

Transpose?

hi i have :

ID   Date    counter  prefix
101  date1   1        out
101  date2   2        out
101  date3   3        out
101  date5   1        in
101  date6   2        in
101  date7   3        in
101  date8   4        in

i want

ID   newvar1   newvar2
101   date1     date5
101   date2     date6
101   date3     date7
101     .         date8

Thanks

Accepted Solutions
Solution
‎05-02-2013 12:47 PM
PROC Star
Posts: 8,164

Re: Transpose?

If you want to use proc transpose, I would suggest something like:

data have;

input ID   Date \$ counter  prefix \$;

cards;

101  date1   1        out

101  date2   2        out

101  date3   3        out

101  date5   1        in

101  date6   2        in

101  date7   3        in

101  date8   4        in

;

proc sort data=have;

by id counter;

run;

proc format;

value \$prefix

'out'=1

'in'=2

;

run;

proc transpose data=have

out=want (drop=_name_ counter)

prefix=newvar;

by id counter;

var date;

id prefix;

format prefix \$prefix.;

run;

All Replies
Contributor
Posts: 22

Re: Transpose?

from what you showed here, it seems NOT a transpose question. You can use ' lag3' to solve your problem.

data transpose1;

input ID   Date \$    counter  prefix;

cards;

101  date1   1        out

101  date2   2        out

101  date3   3        out

101  date5   1        in

101  date6   2        in

101  date7   3        in

101  date8   4        in

run;

data transpose2;

set transpose1;

date5 = lag3(date);

run;

Super Contributor
Posts: 1,041

Re: Transpose?

But the other ID's may not have the exact scenario..they might have less or more counters!!!

Could you help in that scenario also???

Thanks

Contributor
Posts: 22

Re: Transpose?

show me more scenarios.

Contributor
Posts: 22

Re: Transpose?

data t1;

input ID   Date \$    counter  prefix \$;

cards;

101  date1   1        out

101  date2   2        out

101  date3   3        out

101  date5   1        in

101  date6   2        in

101  date7   3        in

101  date8   4        in

;

run;

proc sort data = t1 out = t2;

by id prefix;

run;

proc transpose data=t2 out= t3 prefix = prefix;

by id prefix;

id counter;

var date;

run;

proc transpose data=t3 out= t4 prefix=prefix ;

by id;

var prefix1 - prefix4;

run;

Solution
‎05-02-2013 12:47 PM
PROC Star
Posts: 8,164

Re: Transpose?

If you want to use proc transpose, I would suggest something like:

data have;

input ID   Date \$ counter  prefix \$;

cards;

101  date1   1        out

101  date2   2        out

101  date3   3        out

101  date5   1        in

101  date6   2        in

101  date7   3        in

101  date8   4        in

;

proc sort data=have;

by id counter;

run;

proc format;

value \$prefix

'out'=1

'in'=2

;

run;

proc transpose data=have

out=want (drop=_name_ counter)

prefix=newvar;

by id counter;

var date;

id prefix;

format prefix \$prefix.;

run;

Super Contributor
Posts: 1,041

Re: Transpose?

Thanks so very much. It works well.But is there any other method other than the transpose??which is the beest on eto go for in such situations??

Thanks

PROC Star
Posts: 8,164

Re: Transpose?

Given data like your test data, I'd think that the proc transpose solution would require the least upkeep for the code.

Posts: 3,167

Re: Transpose?

FWIW, Here is a Hash() approach,

data have;

input ID Date \$ counter prefix \$;

cards;

101 date1 1 out

101 date2 2 out

101 date3 3 out

101 date5 1 in

101 date6 2 in

101 date7 3 in

101 date8 4 in

;

data _null_;

if _n_=1 then do;

if 0 then set have;

length newvar1 newvar2 \$ 8;

declare hash h(ordered: 'y');

h.definekey('id','counter');

h.definedata('id', 'newvar1', 'newvar2');

h.definedone();

end;

set have end=last;

rc=h.find();

if prefix='out' then newvar1=date;

else if prefix='in' then newvar2=date;

rc=h.replace();

if last then h.output(dataset:'want');

run;

Haikuo

Super User
Posts: 10,782

Re: Transpose?

Or using self-merge skill:

```data have;
input ID   Date \$ counter  prefix \$;
cards;
101  date1   1        out
101  date2   2        out
101  date3   3        out
101  date5   1        in
101  date6   2        in
101  date7   3        in
101  date8   4        in
;
run;
data want(drop=prefix);
merge have(keep=id date prefix rename=(date=date1) where=(prefix='out'))
have(keep=id date prefix rename=(date=date2) where=(prefix='in'));
by id;
output;
call missing(of _all_);
run;
```

Dr. Arthur.T , glad to see you , How is SGF2013 being ?

Ksharp

PROC Star
Posts: 8,164

Re: Transpose?

: SGF was fantastic!  Our transpose paper was extremely well received, had almost 200 attendees, and I've been asked to present a 50 minute version this September at MWSUG as one of their featured presentations.  I didn't mention it in this thread as I didn't think it would help the OP but, if anyone is interested in knowing how to speed up transpositions up to 50 times faster than one can with proc transpose, take a look at:

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

Super User
Posts: 10,782

Re: Transpose?

Dr Arthur.T,

Nice. glad to see you continue to make contributions for SAS users.

Ksharp

🔒 This topic is solved and locked.