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.
What if a member has more than two entries? Which id should be set as id2?
one member has multiple ID's so when ID repeats then that should come in ID2.
@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?
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.
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;
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;
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
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;
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: