What is the result supposed to look like? Does each Excel file represent a different topic? Does each only have the response identification, number of responses, two text fields and a category or do they change content layout between files?
Are you planning on combining this data together? Are the response IDs the same across the data sets?
If yes to all of those then it may be possible to generate a similar program to read each of those files but the first step is going to be to save them as CSV files so you control reading them. The Number of responses and categores may need a different variable name in each to differentiate when combined. You will also want to identify the longest (most characters used) in each of the columns response id, and the two text columns. The second row would provide LABELS for the variables.
The generic plan would be to read all of the files and then merge or join on the response ID field.
data QID122;
infile "path/qid122Page1.csv" dlm=',' lrecl=500 obs=3;
informat _recordid $25.
QID122_Num best5.
QID122_text $50. /* the 50 and 100 are "guesses" as to possible maximum lengths*/
QID124_text $100. /* for these to values, set as needed */
QID122_Cat best5.
;
input
_recordid
QID122_Num
QID122_text
QID124_text
QID122_Cat
;
label
_recordid ='Response Id'
QID122_Num ="Number of Q122 Responses"
QID122_text ="Q122 - What is the name of this item?"
QID124_text ="Q124 - In the space below, list as many creative uses as you can think of for this..."
QID122_Cat ="Q122 category"
;
run;
Replace the dataset name, infile name to reflect the saved CSV file and various 122 and 124 pieces as needed to read each file. This is the part that is simplified by identifying the longest value across the files.
One way to combine the data (if the response Ids are matching between the sheets):
Proc sort data=QID122;
by responseId;
run;
/* repeat sort for each data set*/
data combined;
merge
QID122
QID125
/* place all of the data set names here*/
;
by responseid;
run;
... View more