Table transformation - multiple values within single field

[Batta]

Hello SAS experts,

I hope you can help me with this: I need to transfer Table 1 in a way shown in Table 2. How can I do this using either proc sql or any other way?

Thank you very much,

Batta

Table 1

ID	VisitNo	OBJNo
ID1	V1	OBJ1
ID1	V2	OBJ1
ID1	V3	OBJ1
ID2	V1	OBJ2
ID2	V2	OBJ2
ID3	V1	OBJ3
ID3	V2	OBJ3
ID3	V3	OBJ3

Table 2

ID	VisitNo	OBJNo
ID1	V1, V2, V3	OBJ1
ID2	V1, V2	OBJ2
ID3	V1, V2, V3	OBJ3

[PeterClemmensen]

data have;
input ID $ VisitNo $ OBJNo $;
datalines;
ID1 V1 OBJ1 
ID1 V2 OBJ1 
ID1 V3 OBJ1 
ID2 V1 OBJ2 
ID2 V2 OBJ2 
ID3 V1 OBJ3 
ID3 V2 OBJ3 
ID3 V3 OBJ3 
;

data want;
   set have(rename = VisitNo = v);
   by ID;
   length VisitNo $200;

   if first.ID then VisitNo = '';

   VisitNo = catx(', ', VisitNo, v);

   if last.ID;
   
   retain VisitNo;
   drop v;
run;

[Batta]

Thank you very much for your time Sir,

I'll check this as soon I can. I hope this will work. 

Kind regards!

[ballardw]

Please tell us what you can do with that new data set structure that you can't with the existing one.

We get a similar request nearly weekly and seldom get an answer to this.

[Batta]

Hi,

I'll be glad to reply to your question and explain this:

First, I agree with you, I am quite aware that date presented in Table 2 cannot be of any use for further data analysis neither they give additional information compared to Table 1, BUT I usually need to give a summary to clinical data mangers and clinical coordinators and for them Table 1 data can be very "bulky" and difficult to handle and here is why: For example, imagine that we have patients Pat1, Pat2, Pat3..., and that each of them has Visit01 - Visit10, and within each visit exactly the same questions they need to answer with simple Yes or No, and that data managers/clinical coordinators want to see during what visits they answered Yes and during what visits they answered No (maybe they are supposed to answer all visits in certain way but, got some reason, clinical coordinators mistakenly entered No... who knows... not to go into details). So data managers/clinical coordinators, in first table, they would have to deal with maybe 300 entries (lets say 30 patients each with 10 visits) and they may find that very cumbersome, while in the Table 2 they are going to have 30 patients where they are going to be able to easily spot any patient that has the same question answered or recorded differently then expected. This is simplified explanation, the number of patients can be much higher, as well as number of questions... but I didn't want to go into too many details and make my original question even more complicated as this was all I wanted to find out, then I'll go over it  and apply the method further. Please let me know if you have any additional question or if my question became to messy. Thank you so much for your time.

All the best!

[Tom · Accepted solution]

You need to use BY group processing.  You will need a NEW variable. Make to make it long enough the hold the new strings.  You can also rename if you want.

data have;
  input ID $ VisitNo $ OBJNo $;
cards;
ID1 V1 OBJ1
ID1 V2 OBJ1
ID1 V3 OBJ1
ID2 V1 OBJ2
ID2 V2 OBJ2
ID3 V1 OBJ3
ID3 V2 OBJ3
ID3 V3 OBJ3 
;

data want;
do until(last.objno);
  set have;
  by id objno;
  length new $100;
  new=catx(',',new,visitno);
end;
  drop visitno;
  rename new=visitno;
run;

proc print;
run;

Obs    ID     OBJNo    visitno

 1     ID1    OBJ1     V1,V2,V3
 2     ID2    OBJ2     V1,V2
 3     ID3    OBJ3     V1,V2,V3

You might want to just give them a report:

proc report data=have;
 column id (objno n),visitno;
 define id / group;
 define visitno/across ' ';
 define objno / display ' ';
 define n / noprint;
run;

Result

  ID        V1        V2        V3
  ID1       OBJ1      OBJ1      OBJ1
  ID2       OBJ2      OBJ2
  ID3       OBJ3      OBJ3      OBJ3

[Batta]

Thank you very much. 

Far more than I expected! 🙂

Kind regards,

Batta 

[Batta]

This kind or report would work too!
Thank you!
Kind regards.

[ballardw]

---

@Batta wrote:

Hi,

I'll be glad to reply to your question and explain this:

First, I agree with you, I am quite aware that date presented in Table 2 cannot be of any use for further data analysis neither they give additional information compared to Table 1, BUT I usually need to give a summary to clinical data mangers and clinical coordinators and for them Table 1 data can be very "bulky" and difficult to handle and here is why: For example, imagine that we have patients Pat1, Pat2, Pat3..., and that each of them has Visit01 - Visit10, and within each visit exactly the same questions they need to answer with simple Yes or No, and that data managers/clinical coordinators want to see during what visits they answered Yes and during what visits they answered No (maybe they are supposed to answer all visits in certain way but, got some reason, clinical coordinators mistakenly entered No... who knows... not to go into details). So data managers/clinical coordinators, in first table, they would have to deal with maybe 300 entries (lets say 30 patients each with 10 visits) and they may find that very cumbersome, while in the Table 2 they are going to have 30 patients where they are going to be able to easily spot any patient that has the same question answered or recorded differently then expected. This is simplified explanation, the number of patients can be much higher, as well as number of questions... but I didn't want to go into too many details and make my original question even more complicated as this was all I wanted to find out, then I'll go over it  and apply the method further. Please let me know if you have any additional question or if my question became to messy. Thank you so much for your time.

All the best!

---

Which describes a need for a report.

Possibly:

data have;
  input ID $ VisitNo $ OBJNo $;
cards;
ID1 V1 OBJ1
ID1 V2 OBJ1
ID1 V3 OBJ1
ID2 V1 OBJ2
ID2 V2 OBJ2
ID3 V1 OBJ3
ID3 V2 OBJ3
ID3 V3 OBJ3 
;

proc report data=have;
   columns id visitno objno;
   define id/ group;
   define visitno / across;
   define objno/group;
run;

I submit that this report is much easier to identify similarity/difference of visit values that a comma separated list. Especially by the time you get to some "patients" with 15 or more visits.