topic How to add duplicates in a new column in SAS Programming

How to add duplicates in a new column

Aleixo — Tue, 27 Feb 2018 18:36:09 GMT

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

Re: How to add duplicates in a new column

Reeza — Tue, 27 Feb 2018 18:40:26 GMT

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

Re: How to add duplicates in a new column

Aleixo — Tue, 27 Feb 2018 18:42:25 GMT

Thanks for your answer

I am using EG. I will study transpose

Re: How to add duplicates in a new column

sam369 — Tue, 27 Feb 2018 18:51:20 GMT

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;

Re: How to add duplicates in a new column

SuryaKiran — Tue, 27 Feb 2018 18:52:14 GMT

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;

Re: How to add duplicates in a new column

novinosrin — Tue, 27 Feb 2018 18:55:23 GMT

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;

Re: How to add duplicates in a new column

Reeza — Tue, 27 Feb 2018 18:56:40 GMT

This gets you fairly close.

Re: How to add duplicates in a new column

Satish_Parida — Tue, 27 Feb 2018 19:00:19 GMT

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.

Re: How to add duplicates in a new column

Aleixo — Wed, 28 Feb 2018 10:17:48 GMT

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