topic ID value occurs twice in the same by group in New SAS User

ID value occurs twice in the same by group

tan-wongv — Thu, 14 Mar 2024 03:40:26 GMT

I have longitudinal data regarding activity of daily living.

This is how my data looks like:

DMRN	recorded_time	item_name	mean_value
8	01NOV2012	Feeding	independent
8	01NOV2012	Grooming	independent
8	01NOV2012	Walking	independent
8	01JUL2013	Feeding	independent
8	01JUL2013	Grooming	independent
8	01JUL2013	Walking	needs assistance
10	07JAN2019	Feeding	independent
10	07JAN2019	Grooming	independent
10	07JAN2019	Walking	independent

I would like to transpose the data into:

DMRN	recorded_time	Feeding	Grooming	Walking
8	01NOV2012	independent	independent	independent
8	01JUL2013	independent	independent	needs assistance
10	07JAN2019	independent	independent	independent

I tried these following codes but it didn't work out because there are too many values occur in the same by group.

proc sort data = ADL;
by dmrn recorded_time;
run;
*Transpose data;
proc transpose data = ADL out = ADL_flipped;
by dmrn;
id item_name;
var mean_value;
run;

Can anyone please help me with this issue? Thank you very much.

Re: ID value occurs twice in the same by group

Patrick — Thu, 14 Mar 2024 04:48:58 GMT

Add your date variable also to the Proc Transpose BY statement.

data ADL;
  infile datalines truncover;
  input DMRN recorded_time:date9. item_name:$10. mean_value:$12.;
  format recorded_time date9.;
  datalines;
8  01NOV2012 Feeding independent
8  01NOV2012 Grooming independent
8  01NOV2012  Walking independent
8 01JUL2013 Feeding independent
8 01JUL2013 Grooming independent
8 01JUL2013  Walking needs assistance
10 07JAN2019 Feeding independent
10 07JAN2019 Grooming independent
10 07JAN2019  Walking independent
;

proc sort data = ADL;
  by dmrn recorded_time;
run;

/*Transpose data*/
proc transpose data = ADL out = ADL_flipped(drop=_name_);
  by dmrn recorded_time;
  id item_name;
  var mean_value;
run;

Re: ID value occurs twice in the same by group

tan-wongv — Thu, 14 Mar 2024 07:44:55 GMT

Thank you for your response! I tried your code after I had fixed some problems with my data and it worked out!