How to add duplicates in a new column

[Aleixo]

Hi,

I try to search how to merge horizontally but from only one data and never found. This happen to me because i use function append

The idea is making a historical data, for example: when find multiple tags add the older date to a new column.

I have this DATA:

Name       Tag     Date

Ze          A      10Jan2018   
Ze          A      1Jan2018
Ze          B      15Fev2018
Manel       C      3Mar2017
Joao        D      20Fev2018
Joao        D      10Fev2018
Joao        D      3Fev2018
Joao        E      25Fev2018
Antonio     F      25Dec2018

I want this DATA:

Name       Tag     Date1             Date2              Date3

Ze          A      10Jan2018         1Jan2018
Ze          B      15Fev2018
Manel       C      3Mar2017
Joao        D      20Fev2018         10Fev2018          3Fev2018
Joao        E      25Fev2018
Antonio     F      25Dec2018

It is possible to do with a task computed new columns? Or somebody have a macro for that?

Thanks for your atention,

Aleixo

[Reeza]

Try the TRANSPOSE task instead.

Are you using EG or SAS Studio?

Either should have a GUI task for this. 

If you're writing code, look at PROC TRANSPOSE.

---

@Aleixo wrote:

Hi,

I try to search how to merge horizontally but from only one data and never found. This happen to me because i use function append

The idea is making a historical data, for example: when find multiple tags add the older date to a new column.

I have this DATA:

Name       Tag     Date

Ze          A      10Jan2018   
Ze          A      1Jan2018
Ze          B      15Fev2018
Manel       C      3Mar2017
Joao        D      20Fev2018
Joao        D      10Fev2018
Joao        D      3Fev2018
Joao        E      25Fev2018
Antonio     F      25Dec2018

I want this DATA:

Name       Tag     Date1             Date2              Date3

Ze          A      10Jan2018         1Jan2018
Ze          B      15Fev2018
Manel       C      3Mar2017
Joao        D      20Fev2018         10Fev2018          3Fev2018
Joao        E      25Fev2018
Antonio     F      25Dec2018

It is possible to do with a task computed new columns? Or somebody have a macro for that?

Thanks for your atention,

Aleixo

---

[Aleixo]

Thanks for your answer

I am using EG. I will study transpose

[SuryaKiran]

Hi,

First sort the data by Name, Tag, Date so that you will have the most recent date in the first column and then Transpose it.

DATA Have;
INFORMAT DATE DATE9.;
FORMAT DATE DATE9.;
INFILE DATALINES DLM="," missover;
INPUT NAME $ TAG $ DATE ;
DATALINES;
Ze,A,10Jan2018
Ze,A,1Jan2018
Ze,B,15Feb2018
Manel,C,3Mar2017
Joao,D,20Feb2018
Joao,D,10Feb2018
Joao,D,3Feb2018
Joao,E,25Feb2018
Antonio,F,25Dec2018
;
RUN;

PROC SORT DATA=Have;
by Name Tag descending date;
run;
PROC TRANSPOSE DATA=Have out=Want(Drop=_NAME_) prefix=Date_ ;
by name tag;
run;

[novinosrin]

data have;
input Name  $      Tag $    Date :date9.;
format date date9.;
datalines;
Ze          A      10Jan2018   
Ze          A      1Jan2018
Ze          B      15Feb2018
Manel       C      3Mar2017
Joao        D      20Feb2018
Joao        D      10Feb2018
Joao        D      3Feb2018
Joao        E      25Feb2018
Antonio     F      25Dec2018
;

proc transpose data=have out=want(drop=_name_) prefix=Date;
by name tag notsorted;
var date;
run;

[Reeza · Accepted solution]

This gets you fairly close. 

[Aleixo]

Thank you all answers people.

I try with that task but the table is repeated 2 times. The columns are repeated(2x) with Date, and the goal is one time only.

I will continue try to understand where is the problem.

Regards,

Aleixo

[Satish_Parida]

This works.

data have;
input Name:$9. Tag:$1. Date:date9.;
format date date9.;
datalines;
Ze          A      10Jan2018   
Ze          A      01Jan2018
Ze          B      15Feb2018
Manel       C      03Mar2017
Joao        D      20Feb2018
Joao        D      10Feb2018
Joao        D      03Feb2018
Joao        E      25Feb2018
Antonio     F      25Dec2018
;
run;

proc sort data=have;
by name tag;
run;

proc transpose data=have out=want(drop=_name_) prefix=Date;
by name tag;
run;

Please let us know if it worked for you.

[sam369]

Hi Aleixo,

data have;
format date date9.;
informat date date9.;
input Name $      Tag $    Date;
cards;
Ze          A      10Jan2018   
Ze          A      1Jan2018
Ze          B      15Feb2018
Manel       C      3Mar2017
Joao        D      20Feb2018
Joao        D      10Feb2018
Joao        D      3Feb2018
Joao        E      25Feb2018
Antonio     F      25Dec2018
;run;

proc sort data=have; by tag name descending date;run;

data temp;
  set have;
   by tag name descending date;
   if first.tag then ord= 0;
   ord+1;
run;


proc transpose data=temp out=want(drop=_:) prefix=date;
 by tag name;
 var date;
 id ord;
run;