Help using Base SAS procedures

Split dataset columnwise with constant columns for each dataset

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Split dataset columnwise with constant columns for each dataset

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;


Accepted Solutions
Solution
‎09-28-2015 03:00 AM
Super User
Super User
Posts: 7,976

Re: Split dataset columnwise with constant columns for each dataset

[ Edited ]

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.

    

 

View solution in original post


All Replies
Solution
‎09-28-2015 03:00 AM
Super User
Super User
Posts: 7,976

Re: Split dataset columnwise with constant columns for each dataset

[ Edited ]

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.

    

 

Frequent Contributor
Posts: 144

Re: Split dataset columnwise with constant columns for each dataset

[ Edited ]

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;

Super User
Super User
Posts: 7,976

Re: Split dataset columnwise with constant columns for each dataset

Posted in reply to arodriguez

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.

Valued Guide
Posts: 860

Re: Split dataset columnwise with constant columns for each dataset

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;

New Contributor
Posts: 3

Re: Split dataset columnwise with constant columns for each dataset

Posted in reply to Steelers_In_DC

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

Super User
Posts: 11,343

Re: Split dataset columnwise with constant columns for each dataset

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

New Contributor
Posts: 3

Re: Split dataset columnwise with constant columns for each dataset

Yes a new response to the survey

Super User
Posts: 11,343

Re: Split dataset columnwise with constant columns for each dataset

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 679 views
  • 0 likes
  • 5 in conversation