Adding one variable to dataset

[india2016]

I have dataset 

 member  id         date

101           1      12Jun2017

102           3      14May2017

101           2      19July2017

103           5      24Jun2017

102           4       13May2017

I want output as 

member  id   id2        date 

101           1    2       12Jun2017

102           3    4        14May2017

103           5               24Jun2017

please suggest any solution.

[Kurt_Bremser]

What if a member has more than two entries? Which id should be set as id2?

[india2016]

one member has multiple ID's so when ID repeats then that should come in ID2.

[Kurt_Bremser]

---

@india2016 wrote:

one member has multiple ID's so when ID repeats then that should come in ID2.

---

WHICH of the repeats should end up in id2? The first, the last, one based on some kind of precedence?

[RW9]

What to do with date here?  Your main part is a simple transpose, however you have different dates for the visits.  Is it just one visit or possibly more?  If you only want one date, then sort the data, transpose up by member, then merge date of first back on.

[india2016]

Actually I want unique member and his multiple ID's should comes in front of 'Member'..


INFORMATION ABOUT "Date"

which date occurs first that should comes against' Member'..

[Kurt_Bremser]

You're still writing Klingon.

Does this come near:

data have;
input member id $ date:date9.;
format date date9.;
cards;
101 1 12Jun2017
102 3 14May2017
101 2 19Jul2017
103 5 24Jun2017
102 4 13May2017
run;

proc sort data=have;
by member id;
run;

data want;
set have (rename=(id=old_id date=old_date));
by member;
retain id id2 date;
format date date9.;
if first.member
then do;
  date = old_date;
  id = old_id;
  id2 = ' ';
end;
else id2 = old_id;
if last.member then output;
drop old_id old_date;
run;

[Ksharp]

data have;
input member id $ date:date9.;
format date date9.;
cards;
101 1 12Jun2017
102 3 14May2017
101 2 19Jul2017
103 5 24Jun2017
102 4 13May2017
;
run;
proc sort data=have;
 by member id;
run;
data temp;
 set have;
 by member;
 retain d;
 if first.member then d=date;
 drop date;
 format d date9.;
run;
proc transpose data=temp out=want prefix=id;
 by member d;
 var id;
run;

[MikeZdeb]

Hi, another idea (could rename DATE_1 and ID variables with PROC DATASETS or as a data set option for data set Y in PROC SUMMARY) ...

data x;
input member id :$1. date :date9.;
format date date9.;
datalines;
101 1 12Jun2017
102 3 14May2017
101 2 19Jul2017
103 5 24Jun2017
102 4 13May2017
;

proc summary data=x nway;
class member;
output out=y (keep=member id: date_1) idgroup(out[2] (date id)=);
run;

DATA SET: y

member  date_1   id_1 id_2

 101   12JUN2017  1    2
 102   14MAY2017  3    4
 103   24JUN2017  5

[jdwaterman91]

Piggybacking off of what @MikeZdeb has, here are a couple of other options/things to consider.

In case your data is not sorted, I would recommend sorting the data so that the date displayed is the earliest date (Assuming that is what you are looking for).

Also you can create a macro variable in case your dataset has more than two records per member.  The macro variable will resolve to the maximum number of records for a member (In the instance below it would be 3, since there are 3 records for member "101".  For your initial dataset, it would be 2 since both members "101" and "102" have 2 records).  

I have added an additional line of data to your data for illustration purposes.

data old;
input member id date :date9.;
format date date9.;
datalines;
101 6 23Oct2017    /* Additional line of data */
101 1 12Jun2017
102 3 14May2017
101 2 19Jul2017
103 5 24Jun2017
102 4 13May2017
;
run;

Proc Sort data = old;
By member id date;
run;

/* Creates your macro variable (n) */
Proc Sql noprint;
select max(n) into :n
from (select count(*) as n from old group by member);
quit;

/* Creates the output */
proc summary data=old nway;
class member;
output out=new (keep=member id: date_1) idgroup(out[&n] (id date)=);
run;

[MikeZdeb]

And you can do some reading for more explanation ...

Transposing Data Using PROC SUMMARY'S IDGROUP Option (John King, Mike Zdeb)

http://support.sas.com/resources/papers/proceedings10/102-2010.pdf