topic Re: Adding one variable to dataset in SAS Programming

Adding one variable to dataset

india2016 — Mon, 03 Jul 2017 09:09:12 GMT

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.

Re: Adding one variable to dataset

Kurt_Bremser — Mon, 03 Jul 2017 09:22:35 GMT

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

Re: Adding one variable to dataset

RW9 — Mon, 03 Jul 2017 09:53:21 GMT

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.

Re: Adding one variable to dataset

india2016 — Mon, 03 Jul 2017 09:59:55 GMT

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

Re: Adding one variable to dataset

Kurt_Bremser — Mon, 03 Jul 2017 10:16:38 GMT

@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?

Re: Adding one variable to dataset

india2016 — Mon, 03 Jul 2017 10:26:57 GMT

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'..

Re: Adding one variable to dataset

Kurt_Bremser — Mon, 03 Jul 2017 10:41:14 GMT

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;

Re: Adding one variable to dataset

Ksharp — Mon, 03 Jul 2017 13:25:40 GMT

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;

Re: Adding one variable to dataset

MikeZdeb — Mon, 03 Jul 2017 15:32:47 GMT

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

Re: Adding one variable to dataset

jdwaterman91 — Mon, 03 Jul 2017 17:08:37 GMT

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;

Re: Adding one variable to dataset

MikeZdeb — Mon, 03 Jul 2017 18:26:44 GMT

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