topic Re: Transposing data from long to wide in SAS Programming

Transposing data from long to wide

Rou — Mon, 11 Mar 2024 16:15:03 GMT

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;

Re: Transposing data from long to wide

Tom — Mon, 11 Mar 2024 16:22:06 GMT

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.

Re: Transposing data from long to wide

PaigeMiller — Mon, 11 Mar 2024 16:53:46 GMT

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.

Re: Transposing data from long to wide

Rou — Mon, 11 Mar 2024 17:02:10 GMT

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!

Re: Transposing data from long to wide

Reeza — Mon, 11 Mar 2024 17:08:23 GMT

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.

Re: Transposing data from long to wide

PaigeMiller — Mon, 11 Mar 2024 17:10:17 GMT

@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.

Re: Transposing data from long to wide

Rou — Mon, 11 Mar 2024 17:17:44 GMT

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

Re: Transposing data from long to wide

PaigeMiller — Mon, 11 Mar 2024 17:25:46 GMT

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.

Re: Transposing data from long to wide

Tom — Mon, 11 Mar 2024 18:16:43 GMT

@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;

Re: Transposing data from long to wide

Patrick — Mon, 11 Mar 2024 21:03:19 GMT

@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.