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

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

11 REPLIES 11
jf
Fluorite | Level 6 jf
Fluorite | Level 6

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;

robertrao
Quartz | Level 8

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

jf
Fluorite | Level 6 jf
Fluorite | Level 6

show me more scenarios.

jf
Fluorite | Level 6 jf
Fluorite | Level 6

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;

art297
Opal | Level 21

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;

robertrao
Quartz | Level 8

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

art297
Opal | Level 21

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

Haikuo
Onyx | Level 15

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

Ksharp
Super User

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

art297
Opal | Level 21

: 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

Ksharp
Super User

Dr Arthur.T,

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

Ksharp

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
  • 11 replies
  • 1581 views
  • 7 likes
  • 5 in conversation