Transposing data from long to wide

[Rou]

Hello,

I am trying to transpose my dataset from long to wide, but I keep getting errors due to reoccurring ID values.

Context: it is survey data taken over 5-year period so there are multiple records per participant

questionID: Id num for each question name

QuestionName= are the question name for each question

ID= unique patient identifier

Here are my codes:

proc sort data= data;
by ID QuestionName QuestionID ResponseDate ResponseTime;
run;
/*Filtering data per unique questionID by ID*/
data want;
set data;
by ID QuestionId;
if QuestionId not in (101569, 101570); /*same question but duplicate ID*/
if first.QuestionId;
run;

proc sort data= want; 
by ID QuestionName;
run;

/*Transposing filtered data into wide format*/
proc transpose data= want = wide_data (drop=_NAME_);
by ID;
id QuestionName;
var Response;
run;

[Tom]

If you have it multiple times per person then include another variable in the BY statement that indicates the time the data was collected.

What are the values of the variable(s) you have in the ID statement?  Those will be used to create the variable names, so they cannot exceed 32 bytes total or they will be truncated.  Truncation could cause duplicates.

[Rou]

VarName exceeds the 32-byte limit, leading to a duplicate once truncated because some of the var names are not unique. How can I address this issue in SAS? Thanks!

[Reeza]

Map the names to shorter names so that the problem goes away.

You could leave the truncated names and add in ID labels that are unique but it's annoying.

[PaigeMiller]

---

@Rou wrote:

VarName exceeds the 32-byte limit, leading to a duplicate once truncated because some of the var names are not unique. How can I address this issue in SAS? Thanks!

---

Use the long data set, not a wide data set; create a report with either PROC REPORT or PROC TABULATE, and there is no 32 byte limit on column headings.

[Tom · Accepted solution]

---

@Rou wrote:

VarName exceeds the 32-byte limit, leading to a duplicate once truncated because some of the var names are not unique. How can I address this issue in SAS? Thanks!

---

Sounds like you are using the wrong variable in the ID statement.

You probably want something like this:

proc sort data= TALL ;
  by ID ResponseDate ResponseTime QuestionID ;
run;
proc transpose data=TALL out=WIDE(drop=_name_) prefix=Q ;
  by ID ResponseDate ResponseTime ;
  id QuestionID ;
  idlabel QuestionName;
  var Response ;
run;

[PaigeMiller]

Leave this data in the long format. Most SAS procedures work with data in the long format. If you want it wide because you are preparing some sort of report, please say that, and we can provide solutions that take a long data set and make a wide report.

[Rou]

I am preparing a report, so I need it in a wide format. 

[PaigeMiller]

Can you please provide an example data set (it doesn't have to be large, but it does have to be representative of your problem) and what you want the output to look like.

[Patrick]

---

@Rou wrote:

I am preparing a report, so I need it in a wide format. 

---

Most SAS Procs like Proc Report and Proc Tabulate work much better with long data. It's then the report procedure that allows you to create wide cross-tab reports.