topic Re: Split dataset columnwise with constant columns for each dataset in SAS Procedures

Split dataset columnwise with constant columns for each dataset

Deep1 — Wed, 23 Sep 2015 13:15:47 GMT

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;

Re: Split dataset columnwise with constant columns for each dataset

RW9 — Wed, 23 Sep 2015 13:26:35 GMT

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.

Re: Split dataset columnwise with constant columns for each dataset

arodriguez — Wed, 23 Sep 2015 13:26:50 GMT

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;

Re: Split dataset columnwise with constant columns for each dataset

RW9 — Wed, 23 Sep 2015 13:28:01 GMT

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.

Re: Split dataset columnwise with constant columns for each dataset

Steelers_In_DC — Wed, 23 Sep 2015 13:44:11 GMT

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;

Re: Split dataset columnwise with constant columns for each dataset

ballardw — Wed, 23 Sep 2015 14:57:33 GMT

By response do you mean a new respondent to the survey?

Re: Split dataset columnwise with constant columns for each dataset

Deep1 — Mon, 28 Sep 2015 07:04:13 GMT

Yes a new response to the survey

Re: Split dataset columnwise with constant columns for each dataset

Deep1 — Mon, 28 Sep 2015 07:06:33 GMT

Both the array code and the macro code works like a charm ..!! Thanks everyone for your help 🙂

Re: Split dataset columnwise with constant columns for each dataset

ballardw — Mon, 28 Sep 2015 20:52:57 GMT

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.