Help using Base SAS procedures

how to merge observations with repeated data (format: wide) ?

Reply
N/A
Posts: 1

how to merge observations with repeated data (format: wide) ?

Hello,

New user in SAS, I would like a hand about how to merge observations with repeated data.

Because a good drawing is better than a bad explanation, here are 2 tables:

1°) what I have:

patient number

color

congruence

trial

t1

t2

t3

t4

1

GREEN

NO

ERROR

262

588

510

714

1

RED

NO

ERROR

931

449

307

460

1

BLUE

NO

ERROR

672

654

592

1

YELLOW

NO

ERROR

752

568

1

GREEN

NO

GOOD

743

697

999

1

RED

NO

GOOD

1087

630

590

1

BLUE

NO

GOOD

192

240

1

YELLOW

NO

GOOD

235

756

345

1

GREEN

YES

ERROR

127

345

1

RED

YES

ERROR

873

234

123

638

1

BLUE

YES

ERROR

345

432

765

1

YELLOW

YES

ERROR

567

341

1

GREEN

YES

GOOD

321

795

432

1

RED

YES

GOOD

234

693

1

BLUE

YES

GOOD

321

432

674

412

1

YELLOW

YES

GOOD

324

756

2°) what I (would) want:

patient number

color

congruence

trial

t1

t2

t3

t4

t5

t6

t7

t8

t9

t10

t11

t12

t13

1

null

NO

ERROR

262

588

510

714

931

449

307

460

672

654

592

752

568

1

null

NO

GOOD

743

697

999

1087

630

590

192

240

235

756

345

1

null

YES

ERROR

127

345

873

234

123

638

345

432

765

567

341

1

null

YES

GOOD

321

795

432

234

693

321

432

674

412

324

756

The idea is to merge observations belonging to the same individual, by color. The information about color is not important and this variable can be removed,

unfortunately I cannot write the code to perform this task. Thank you very much if one could help.

ES

Respected Advisor
Posts: 4,173

Re: how to merge observations with repeated data (format: wide) ?

Posted in reply to EmmanuelS

Looking at your data isn't it simply so that you want to "merge" by Patient Number, Congruence and Trial? If so then what I would do:

1) Organize your data in a long format with columns "patient_number, congruence, trial, t"

2) Use Proc Transpose with by group processing "by patient_number congruence trial".

For code examples: Please provide a data step creating your source data set.

Super User
Posts: 10,020

Re: how to merge observations with repeated data (format: wide) ?

Posted in reply to EmmanuelS

good form data are needed to input .

data have;

input patient_number color $ congruence $ trial $ t1 t2 t3 t4 ;

cards;

1 GREEN NO ERROR 262 588 510 714

1 RED NO ERROR 931 449 307 460

1 BLUE NO ERROR 672 654 592 .

1 YELLOW NO ERROR 752 568 . .

1 GREEN NO GOOD 743 697 999 .

1 RED NO GOOD 1087 630 590 .

1 BLUE NO GOOD 192 240 .

1 YELLOW NO GOOD 235 756 345 .

;

run;

proc transpose data=have out=temp;

by patient_number congruence  trial color notsorted;

var t1-t4;

run;

proc transpose data=temp out=want(drop=_Smiley Happy;

by patient_number congruence  trial;

id _name_ color;

var col1;

run;

Xia Keshan

Super User
Super User
Posts: 7,039

Re: how to merge observations with repeated data (format: wide) ?

That is almost what is wanted.  You need to eliminate the ID statement from the last transpose.  Also you need to eliminate the missing values .

data have;

input patient_number color $ congruence $ trial $ t1 t2 t3 t4 ;

cards;

1 GREEN NO ERROR 262 588 510 714

1 RED NO ERROR 931 449 307 460

1 BLUE NO ERROR 672 654 592 .

1 YELLOW NO ERROR 752 568 . .

1 GREEN NO GOOD 743 697 999 .

1 RED NO GOOD 1087 630 590 .

1 BLUE NO GOOD 192 240 . .

1 YELLOW NO GOOD 235 756 345 .

;

run;

proc transpose data=have out=temp(where=(col1 ne .));

  by patient_number congruence  trial color notsorted;

  var t1-t4;

run;

proc transpose data=temp out=want(drop=_Smiley Happy prefix=T;

  by patient_number congruence  trial;

  var col1;

run;

Ask a Question
Discussion stats
  • 3 replies
  • 195 views
  • 0 likes
  • 4 in conversation