Trnaspose the data set

Reply
Occasional Contributor
Posts: 13

Trnaspose the data set

Hi Every one,

I have dataset like

ID  value  obs_date     rank

1     35     2/2/2013       1

1     45     25/1/2013     2    

1     55     24/1/2013     3    

1     65     23/1/2013     4

2     75     22/1/2013     1

2     85     20/1/2013     2

2     95     19/1/2013     3

2     15     18/1/2013     4

I want to transpose this on the ID

IDValue1obs_Date1Value2obs_Date2Value3obs_Date3Value4obs_Date4
1352/2/2013      4525/1/2013      5524/1/2013      6523/1/2013     
27522/1/2013      8520/1/2013      9519/1/2013      1518/1/2013     

Thanks

Frequent Contributor
Posts: 88

Re: Trnaspose the data set

The Below code i haven't tested but it should work hopefully...

Suppose your dataset name is test1


//Transposing first variable  from test1 datatset to Trans1 dataset
proc transpose data=test1 out=trans1 prefix=value;
by ID;
id Rank;
var value;
run;


// Transposing second variable from test1 datatset to Trans2 dataset

proc transpose data=test1 out=trans2 prefix=obs_date;
by ID;
id Rank;
var  obs_date;
run;

// merging both dataset to get actual result.

data Act_trans;
merge trans1 (drop=_name_) trans2 (drop=_name_);
by ID;
run;

//Printing Actual transpose dataset

Proc print data=Act_trans;
run;

Please check once also you can use following link,

http://www.ats.ucla.edu/stat/sas/modules/ltow_transpose.htm

Regards,

Tushar J.

Super Contributor
Posts: 297

Re: Trnaspose the data set

No need to merge here, just a dual transpose.

DATA HAVE;

INFILE DATALINES;

LENGTH ID 8 VALUE 8 OBS_DATE 8 RANK 8;

INPUT @1 ID @7 VALUE @14 OBS_DATE DDMMYY10.  @28 RANK;

FORMAT OBS_DATE DATE9.;

DATALINES;

1     35     02/02/2013     1

1     45     25/01/2013     2

1     55     24/01/2013     3

1     65     23/01/2013     4

2     75     22/01/2013     1

2     85     20/01/2013     2

2     95     19/01/2013     3

2     15     18/01/2013     4

;

RUN;

PROC TRANSPOSE DATA= HAVE OUT=WANT;

  BY ID RANK NOTSORTED;

RUN;

PROC TRANSPOSE DATA= WANT OUT=WANT2  (DROP=_NAME_) DELIMITER=_;

BY ID;

ID _NAME_ RANK;

VAR COL:;

FORMAT OBS_DATE_: DATE9.;

RUN;

Ask a Question
Discussion stats
  • 2 replies
  • 172 views
  • 0 likes
  • 3 in conversation