topic Re: Transposing a long dataset to wide with multiple variable types in SAS Programming

Transposing a long dataset to wide with multiple variable types

NandiniA — Mon, 11 May 2026 19:27:42 GMT

Hi,

I have a dataset with several variable types (char, num, date, time, checkboxes, string) and I am trying to convert it to a wide format with 1 row per ID. I have created RESP_C, RESP_N, RESP_DATE and RESP_TIME variables to assign the correct the appropriate values to each of these columns.

data prepared;
set joined;length RESP_C $1000 RESP_N 8 RESP_DATE 12 RESP_TIME 8;

RESP_C = strip(Response);

select (upcase(Target_Type));

when ('DATE') do;
RESP_DATE = input(strip(Response), anydtdte.);
format RESP_DATE date9.;
RESP_C = "";
end;

when ('TIME') do;
RESP_TIME = input(strip(Response), anydttme.); /* NA 4/29: timepart is for mixed reponse, so removed it (date + time) */
format RESP_TIME timeampm.;
RESP_C = "";
end;

when ('CHECK') do;
if strip(Response) in ('Y','YES','TRUE','1','CHECKED','Selected') then RESP_N = 1; /* NA 4/29: Added 'Selected' to the list as that is the response of the form */
else if missing(Response) then RESP_N = .;
else RESP_N = 0;
/*format RESP_N 1.; */
RESP_C = "";
end;

when ('NUM') do;
RESP_N = input(strip(Response), best32.);
RESP_C = "";
end;

otherwise do; /* CHAR */
/* keep RESP_C */
end;

end;
run;

proc sort data=prepared;
by SID FormDateTime FirstName MiddleName LastName DOB;
run;

/***********************************************************************
* 7) Transpose into wide:
* - Character targets (CHAR) -> wide_char
* - Numeric targets (DATE, TIME, NUM, CHECK) -> wide_num
***********************************************************************/

proc transpose data=prepared(where=(Target_Type='CHAR'))
out=wide_char(drop=_name_);
by SID FormDateTime FirstName MiddleName LastName DOB;
id VarName_ID;
var RESP_C;
run;

proc transpose data=prepared(where=(Target_Type in ('DATE','TIME','NUM','CHECK')))
out=wide_num(drop=_name_);
format RESP_DATE date9. RESP_TIME timeampm.;
by SID FormDateTime FirstName MiddleName LastName DOB;
id VarName_ID;
var RESP_N RESP_DATE RESP_TIME;
run;

However, this is only working on some date values in the dataset and not applying uniformly to all the values. Additionally, after transposing each variable type is a unique row and not one row per ID.

I would appreciate any help in fixing this code. Thank you!

Re: Transposing a long dataset to wide with multiple variable types

Tom — Tue, 12 May 2026 00:45:41 GMT

It looks like you currently have the data stored in a single character variable named RESPONSE.

You appear to also have a variableTARGET_TYPE that appears to indicates what TYPE of variable needs to be created to store this particular RESPONSE.

What variable has the NAME that you want to use for the new variable?

What are the variables that uniquely identify the set of observations you want to transpose into one?

Do you need code that works for this particular dataset, with its specific set of variables?

If so then just write some code that that reads in all of the observations for the output observation in one iteration of the data step.

So something like:

data want;
  do until (last.id);
    set have;
    by id;
    if NAME='DATE' then date=input(left(response),anydtdte.);
    else if NAME='AGE' then age=input(left(response),32.);
    ...
  end;
  drop NAME TARGET_TYPE RESPONSE ;
run;

Or do you want a program that could convert ANY dataset? If so then select the unique combinations of NAME and TARGET_TYPE from the dataset and use that to generate a program like the one above.

For a more detailed answer provide a small example input dataset (as a working DATA step). Also provide a data step that creates the dataset you want to get from that example input.

Re: Transposing a long dataset to wide with multiple variable types

Tom — Tue, 12 May 2026 01:28:34 GMT

Since you are using this BY statement in your PROC TRANPOSE

by SID FormDateTime FirstName MiddleName LastName DOB;

To get duplicate observations you would need to have changes in one of those variables. Are you sure that FORMDATETIME is the same of all observations you want to combine? What about the other 5 varaibles?

Or are you just talking about the fact that you generated TWO datasets, but did not include a step to combine them?

data want;
  merge wide_num wide_char;
  by SID FormDateTime FirstName MiddleName LastName DOB;
run;

Also you will need to apply the FORMAT to the variables AFTEr the PROC TRANSPOSE step.

For example both DATE and TIME variables are numeric, so I would assume you put them into the WIDE_NUM dataset. But if you do not attach a FORMAT to the resulting variable(s) then the values will be hard for humans to interpret.

Re: Transposing a long dataset to wide with multiple variable types

Ksharp — Wed, 13 May 2026 06:23:20 GMT

Check the MERGE skill proposed by me, Arthur.T and Matt :

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

And could you post your sample data and the desired output to explain this question better?

data have;
input id char $ num date :date9. time :time. ;
format date date9. time time.;
cards;
1 ss 23 1jan2010 09:20:21
1 ss 24 2jan2010 09:20:22
1 ss 25 3jan2010 09:20:23
2 ss 26 4jan2010 09:20:24
2 ss 27 5jan2010 09:20:25
;



data temp;
 set have;
 by id;
 if first.id then n=0;
 n+1;
run;
proc sql noprint;
select distinct(catt('temp(where=(n=',n,') rename=(
 char=char_',n,' num=num_',n,' date=date_',n,' time=time_',n,'))'))
 into :merge separated by ' '
   from temp;
quit;
data want;
merge &merge ;
by id;
drop n;
run;

And a more simple and convenient way is using proc summary+idgroup, but it has limit that each group have 100 obs at most.

data have;
input id char $ num date :date9. time :time. ;
format date date9. time time.;
cards;
1 ss 23 1jan2010 09:20:21
1 ss 24 2jan2010 09:20:22
1 ss 25 3jan2010 09:20:23
2 ss 26 4jan2010 09:20:24
2 ss 27 5jan2010 09:20:25
;
proc sql noprint;
select max(n) into :n from
(select count(*) as n  from have group by id);
quit;

proc summary data=have nway;
class id;
output out=want idgroup( out[&n] (char num date time)=);
run;

Re: Transposing a long dataset to wide with multiple variable types

NandiniA — Tue, 12 May 2026 14:09:59 GMT

Thank you so much for your response!

I am trying to write this code so it an work on any dataset and not just this one.

Here is the example dataset I am using:

SID FirstName $ MiddleName $ LastName $ DOB FormName $ FormDateTime Question $ QuestionPosition QuestionType $ Response $

999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 This document serves as a guide for care team members to establish, track, and evaluate treatment goals for clients. It aims to facilitate client-centered care and promote collaboration among multidisciplinary team members. Please note that this document should be re-evaluated post-STAIR/post-WET, diagnoses dependent. Please note if a client is on unit longer than a month this document should be reviewed, re-evaluated, and if necessary updated. 1 Information
999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Program Start Date 2 Date 8/1/2025
999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Language conducted in 3 Text - Short English
999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Time In 4 Text - Short 1:00 PM
999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Time Out 5 Text - Short 1:30 PM
999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Date of Treatment Plan Completion 6 Date 8/15/2025
999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Current Revision Date 7 Date 8/15/2025
999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Plan Version # 8 Text - Short 1
999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Duration (rounded to nearest 15-minute interval) 9 Integer (Number) 30

999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Target Date for Completion 33 Date 10/31/2025
999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Mental Health Treatment Goals 34 Section header
999994 Jeff T-Bars 1/1/2000 Project Evolve Treatment Plan 8/15/2025 13:28 Goal 35 Text Client will actively participate

What variable has the NAME that you want to use for the new variable? : VarName

And here is my complete code:

/***********************************************

* 2) Keep ONE form

***********************************************/

data form1;

set longdata;

where FormName = "Project Evolve Treatment Plan";

run;

/***********************************************************************

* 3) Define a metadata map: question text -> variable name + target type

* target_type: CHAR | DATE | TIME | NUM | CHECK

* - CHECK will become numeric 0/1

* - DATE and TIME become numeric SAS date/time with proper formats

* - CHAR stays character

***********************************************************************/

data meta;

length Question $500 VarName $40 Target_Type $8;

infile datalines dlm='|' truncover;

input Question :$500. VarName :$40. Target_Type :$8.;

datalines;

run;

/****************************************************************************************

* 4) Clean and standardize unmapped questions and join to metadata

* - If a question isn't in meta, auto-generate a safe VarName from the question text

****************************************************************************************/

proc sql;

create table joined as

select a.*,

coalesce(b.VarName,

/* fallback: build a short, SAS-safe name from the question text */

substr(

compress(translate(lowcase(a.Question),'_',' '), , 'kad'),

1, 32)

) as VarName length=40,

upcase(coalesce(b.Target_Type,'CHAR')) as Target_type

from form1 as a

left join meta as b

on a.Question = b.Question

;

quit;

/* Trying to create a new TARGET_TYPE variable that can identify the right variable type */

proc freq data=joined;

tables QuestionType;

run;

/* QuestionType:

Checkbox

Checkbox header

Date

Information

Integer (Number)

Section header

Text

Text - Short

data joined ;

set joined;

if QuestionType="Date" and flag=0 then TARGET_TYPE="DATE";

else if QuestionType="Integer (Number)" and flag=0 then TARGET_TYPE="NUM";

else if QuestionType="Checkbox" and flag=0 then TARGET_TYPE="CHECK";

else if QuestionType="Text - Short" and flag=1 then TARGET_TYPE="TIME";

ELSE;

run;

proc sort data = joined; by QuestionPosition; run;

/****************************************************************************************

* 5) Normalize Response by Target_Type:

* - Create RESP_C (character) and RESP_N (numeric) holders

* - Convert:

* DATE: mm/dd/yyyy -> SAS date

* TIME: e.g., "1:00 PM" -> SAS time

* CHECK: Y/N, Yes/No, True/False, 1/0, Checked/Unchecked -> 1/0 numeric

* NUM: numeric parse

* CHAR: keep as text

****************************************************************************************/

data prepared;

set joined;

length RESP_C $1000 RESP_N 8 RESP_DATE 12 RESP_TIME 8;

/* retain RESP_C; REMVOING THIS as this retains values over rows, which could be causing the error */

RESP_C = strip(Response);

select (upcase(Target_Type));

when ('DATE') do;

RESP_DATE = input(strip(Response), anydtdte.);

format RESP_DATE date9.;

RESP_C = "";

end;

when ('TIME') do;

RESP_TIME = input(strip(Response), anydttme.); /* NA 4/29: timepart is for mixed reponse, so removed it (date + time) */

format RESP_TIME timeampm.;

RESP_C = "";

end;

when ('CHECK') do;

if strip(Response) in ('Y','YES','TRUE','1','CHECKED','Selected') then RESP_N = 1; /* NA 4/29: Added 'Selected' to the list as that is the response of the form */

else if missing(Response) then RESP_N = .;

else RESP_N = 0;

/*format RESP_N 1.; */

RESP_C = "";

end;

when ('NUM') do;

RESP_N = input(strip(Response), best32.);

RESP_C = "";

end;

otherwise do; /* CHAR */

/* keep RESP_C */

end;

run;

/* 5/6: It is not reading some dates, seems to be only reading Aug 15 correctly but not the other mentioned values */

This code is resulting in 2 datasets, one containing character values and one containing numeric values which will be merged. However, the numeric dataset has multiple rows per ID due to the different formats.

proc sort data=prepared;

by SID FormDateTime;

run;

data prepared;

set prepared;

by SID FormDateTime;

if first.FormDateTime then question_seq = 0;

question_seq + 1;

run;

data prepared;

set prepared;

length VarName_ID $60;

VarName_ID = cats(VarName, '_Q', put(question_seq, z2.));

run;

proc sort data=prepared;

by SID FormDateTime question_seq;

run;

/***********************************************

* 6) Sort keys before transposing

***********************************************/

proc sort data=prepared;

by SID FormDateTime FirstName MiddleName LastName DOB;

run;

/***********************************************************************

* 7) Transpose into wide:

* - Character targets (CHAR) -> wide_char

* - Numeric targets (DATE, TIME, NUM, CHECK) -> wide_num

***********************************************************************/

proc transpose data=prepared(where=(Target_Type='CHAR'))

out=wide_char(drop=_name_);

by SID FormDateTime FirstName MiddleName LastName DOB;

id VarName_ID;

var RESP_C;

run;

proc transpose data=prepared(where=(Target_Type in ('DATE','TIME','NUM','CHECK')))

out=wide_num(drop=_name_);

format RESP_DATE date9. RESP_TIME timeampm.;

by SID FormDateTime FirstName MiddleName LastName DOB;

id VarName_ID;

var RESP_N RESP_DATE RESP_TIME;

run;

Re: Transposing a long dataset to wide with multiple variable types

Tom — Tue, 12 May 2026 15:46:01 GMT

Remember to use the Insert Code or Insert SAS Code icons on the forum editor when saying text blocks or code blocks. That will prevent the text from being flowed as paragraphs.

Also when sharing data as text make sure that you have clear delimiters, preferably ones that are visible to humans.

Also when sharing a dataset provide a working data step that can recreate the dataset so that other's can make a copy of your data to program from.

data have;
  infile cards dsd dlm='|' truncover firstobs=2;
  input SID :$9. (FirstName MiddleName LastName) (:$20.)
   DOB :$10. FormName :$32. FormDateTime :anydtdtm. 
   Question :$500. QuestionPosition QuestionType :$30. Response $200.
  ;
  format FormDateTime datetime19.;
cards4;
SID|FirstName|MiddleName|LastName|DOB|FormName|FormDateTime|Question|QuestionPosition|QuestionType|Response|$
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|This document serves as a guide for care team members to establish, track, and evaluate treatment goals for clients. It aims to facilitate client-centered care and promote collaboration among multidisciplinary team members. Please note that this document should be re-evaluated post-STAIR/post-WET, diagnoses dependent. Please note if a client is on unit longer than a month this document should be reviewed, re-evaluated, and if necessary updated.|1|Information
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Program StartDate|2|Date|8/1/2025
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Language conducted in|3|Text - Short|English
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Time In|4|Text - Short|1:00 PM
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Time Out|5|Text - Short|1:30 PM
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Date of Treatment Plan Completion|6|Date|8/15/2025
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Current Revision Date|7|Date|8/15/2025
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Plan Version #|8|Text - Short|1
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Duration (rounded to nearest 15-minute interval)|9|Integer (Number)|30
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Target Date for Completion|33|Date|10/31/2025
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Mental Health Treatment Goals|34|Section header
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Goal|35|Text|Client will actively participate
;;;;

So the first thing you need to do is figure out what variables (questions) appear on the form. If you do not have that available directly from the survey metadata then perhaps you can just summarize what you see in the data file.

proc sql;
 create table variables as
 select cats('VAR',put(QuestionPosition,z2.)) as VarName
      , count(*) as nobs
      , QuestionPosition
      , QuestionType
      , Question
 from have
 group by 1,3,4,5
 ;
run;

Result

That should provide most of what you need to create a program to read the actual responses into those variables. But you are missing some information. SAS stores character variables as FIXED length. So you need some way to decide how long to make 5 text variables. Again if they did not provide any metadata that would describe the variables you could try to GUESS a length based on the lengths of the responses in the file.

proc sql;
 create table variables as
 select cats('VAR',put(QuestionPosition,z2.)) as VarName
      , count(*) as nobs
      , max(length(response)) as Length
      , QuestionPosition
      , QuestionType
      , Question
 from have
 group by Varname,QuestionPosition,QuestionType,Question
 ;
run;

Also some of those "questions" look more like metadata and not actual user responses. So I doubt that you need to carry Questions number 1 and 34 into the output dataset. In fact question 34 looks like perhaps it should be used to decide what DATASET to create so that you have all of the questions from the same section of the survey in the same SAS dataset.

So for this example perhaps you want to generate a data step like this:

data want;
  do until(last.FormDateTime);
    set have;
    by SID -- FormDateTime ;
    
LENGTH    
 VAR02 8
 VAR03 $8
 VAR04 $8 
 VAR05 $8
 VAR06 8
 VAR07 8
 VAR08 $1
 VAR09 8
 VAR33 8
 VAR35 $40
;
LABEL    
 VAR02 ='Program StartDate'
 VAR03 ='Language conducted in'
 VAR04 ='Time In'
 VAR05 ='Time Out'
 VAR06 ='Date of Treatment Plan Completion'
 VAR07 ='Current Revision Date'
 VAR08 ='Plan Version #'
 VAR09 ='Duration (rounded to nearest 15-minute interval)'
 VAR33 ='Target Date for Completion'
 VAR35 ='Goal'
;
 FORMAT
 VAR02 date9.
 VAR06 date9.
 VAR07 date9.
 VAR33 date9.
;  
 SELECT (QuestionPosition);
   WHEN (2) var02 = input(response,mmddyy10.);
   WHEN (3) var03 = response;
   WHEN (4) var04 = response;
   WHEN (5) var05 = response;
   WHEN (6) var06 = input(response,mmddyy10.);
   WHEN (7) var07 = input(response,mmddyy10.);
   WHEN (8) var08 = response;
   WHEN (9) var09 = input(response,32.);
   WHEN (33) var33 = input(response,mmddyy10.);
   WHEN (35) var35 = response;
   OTHERWISE ;
 END;
   end;
   drop question -- response;
run;

Let's print the results (using the labels).

proc print label data=want;
 var SID var: ;
run;

Result

You then just need to work out how to generate that data step from the metadata (or the VARIABLES dataset you generated from the input data). That is not very hard since you can use a data step to write the code to a text file and then just %INCLUDE it to have SAS run it.

Re: Transposing a long dataset to wide with multiple variable types

NandiniA — Tue, 12 May 2026 20:03:03 GMT

Firstly, I apologize for the formatting. Thank you so much for taking the time to explain this and the detailed response.

The code gave me a wide dataset with the right formats!!

I will attempt it on the entire dataset now, including the generating the data step from the metadata, and let you know how it goes.

Again, thank you so much!