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.
... View more
