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
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 25Dec2018It is possible to do with a task computed new columns? Or somebody have a macro for that?
Thanks for your atention,
Aleixo
Thanks for your answer
I am using EG. I will study transpose
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;
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;
This gets you fairly close.
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
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.