DATA Step, Macro, Functions and more

How to add duplicates in a new column

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

How to add duplicates in a new column

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


Accepted Solutions
Solution
‎02-28-2018 07:20 AM
Super User
Posts: 24,004

Re: How to add duplicates in a new column

delete_transpose.JPG

 

This gets you fairly close. 

View solution in original post


All Replies
Super User
Posts: 24,004

Re: How to add duplicates in a new column

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


 

Contributor
Posts: 70

Re: How to add duplicates in a new column

Thanks for your answer

 

I am using EG. I will study transpose

PROC Star
Posts: 629

Re: How to add duplicates in a new column

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;
Thanks,
Suryakiran
Super User
Posts: 2,061

Re: How to add duplicates in a new column

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;
Solution
‎02-28-2018 07:20 AM
Super User
Posts: 24,004

Re: How to add duplicates in a new column

delete_transpose.JPG

 

This gets you fairly close. 

Contributor
Posts: 70

Re: How to add duplicates in a new column

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

Frequent Contributor
Posts: 112

Re: How to add duplicates in a new column

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.

Regular Contributor
Posts: 171

Re: How to add duplicates in a new column

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=_Smiley Happy prefix=date;
 by tag name;
 var date;
 id ord;
run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 170 views
  • 0 likes
  • 6 in conversation