BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Deep1
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

    

 

arodriguez
Lapis Lazuli | Level 10

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Steelers_In_DC
Barite | Level 11

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;

Deep1
Calcite | Level 5

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

ballardw
Super User

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

Deep1
Calcite | Level 5

Yes a new response to the survey

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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