BookmarkSubscribeRSS Feed
india2016
Pyrite | Level 9

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.

10 REPLIES 10
india2016
Pyrite | Level 9

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

Kurt_Bremser
Super User

@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
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9
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
Super User

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
Super User
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
Rhodochrosite | Level 12

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
Obsidian | Level 7

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
Rhodochrosite | Level 12

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

 

 

 

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 1152 views
  • 0 likes
  • 6 in conversation