Hi all,
I have a SAS table with survey responses. When there is a new response, a new column is added. I want to keep first 2 columns as constant and split the tables in below way. The tables needs to be split in this manner for easier import to visualization tool.
Data set:
Category Questions Response1 Response2 Response 3
Split table 1:
Category Questions Response1
Split table 2:
Category Questions Response2
Split table 3:
Category Questions Response3
I have already tried below macro. This is splitting the data row wise. New table for each row. Please help....
%let TABLE=SPLIT2;
%let COLUMN=Questions;
proc sql noprint;
select distinct
cat("DATA out_",compress(&COLUMN.,,'kad'),
"; set &TABLE.(where=(&COLUMN.='", &COLUMN.,
"')); run;") into :allsteps separated by ';'
from &TABLE.;
quit;
%macro runSteps;
&allsteps.;
%mend;
%runSteps;
No need to go all marco language for that, a simple array will do the trick, note I assume from your test data the three variables are called response, and there are 3, so you will need to modify as per your data.
data want (drop=response1-response3);
set have;
length resp $20;
array response{3};
do i=1 to 3;
qnum=i;
resp=response{i};
output;
end;
run;
Note that it is not a great idea to split your data up unless you really have to (i.e. for an output). You are better off creating a normlised table structure as above then using by group processing to work with that data, i.e. by qnum, rather than looping code each time.
No need to go all marco language for that, a simple array will do the trick, note I assume from your test data the three variables are called response, and there are 3, so you will need to modify as per your data.
data want (drop=response1-response3);
set have;
length resp $20;
array response{3};
do i=1 to 3;
qnum=i;
resp=response{i};
output;
end;
run;
Note that it is not a great idea to split your data up unless you really have to (i.e. for an output). You are better off creating a normlised table structure as above then using by group processing to work with that data, i.e. by qnum, rather than looping code each time.
I will use something like this
%MACRO RUNSTEP(DS=);
%LOCAL DSID NVARS RC I;
%let DSID=%sysfunc(open(&DS,is)); %*Open DS;
%let NVARS=%sysfunc(attrn(&DSID,nvars)); %*Count vars in DS;
%let RC=%sysfunc(close(&DSID));%*Close DS;
%DO I=3 %TO &NVARS.;
DATA OUT_&I.;
SET &DS;
KEEP Category Questions Response%eval(&I-2).;
RUN;
%END;
%MEND;
Which illustrates my point above. If you now need to means that data, and output it, then you would need to loop over those datasets you just created, then loop over them again to output. Or you could just use by qnum in the means and the report and have no looping at all.
Here is another solution:
data have;
infile cards dsd;
input category$ question$ response1$ response2$ response3$;
cards;
category,question,one,two,three
;
run;
%macro response;
%do i = 1 %to 3;
data response_&i;
set have(keep=category question response&i);
run;
%end;
%mend;
%response;
Both the array code and the macro code works like a charm ..!! Thanks everyone for your help 🙂
By response do you mean a new respondent to the survey?
Yes a new response to the survey
I would seriously consider why respondents are in column. It sounds like someone may be duplicating an approach used in a spreadsheet in classwork example and never considered the impact on other data.
With respondent as the column it is going to be very difficult to do things like comparing associations between 2 or more questions. I.e what percentage of respondents that picked "A" for question 3 also answered "D" for question 10.
I have worked with survey data upwards of 50,000 respondents and cannot imagine anyway I would have been able to meet requirements with your data structure.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.