BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aleixo
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

delete_transpose.JPG

 

This gets you fairly close. 

View solution in original post

8 REPLIES 8
Reeza
Super User

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
Quartz | Level 8

Thanks for your answer

 

I am using EG. I will study transpose

SuryaKiran
Meteorite | Level 14

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
novinosrin
Tourmaline | Level 20
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
Super User

delete_transpose.JPG

 

This gets you fairly close. 

Aleixo
Quartz | Level 8

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
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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