BookmarkSubscribeRSS Feed
EmmanuelS
Calcite | Level 5

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

3 REPLIES 3
Patrick
Opal | Level 21

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.

Ksharp
Super User

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=_:);

by patient_number congruence  trial;

id _name_ color;

var col1;

run;

Xia Keshan

Tom
Super User Tom
Super User

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=_:) prefix=T;

  by patient_number congruence  trial;

  var col1;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 797 views
  • 0 likes
  • 4 in conversation