BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jonatan_velarde
Lapis Lazuli | Level 10

Good morning:

 

i have this data set:

 

Question Sex yes_no
1 male 1
1 male 1
1 male 0
1 male 0
1 male 0
1 male 0
1 male 0
2 male 1
2 male 1
2 male 1
2 male 0
2 male 0
2 male 0
2 male 0
3 male 1
3 male 1
3 male 1
3 male 1
3 male 1
3 male 0

 

and i need to obtain this one, as follows:

 

Sex Question_1 Question_2 Question_3
male 1 1 1
male 1 1 1
male 0 1 1
male 0 0 1
male 0 0 1
male 0 0 0
male 0 0        .

 

Thanks in advance

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

Hello @jonatan_velarde,

 

Here is an example illustrating NicoM's suggestion:

proc transpose data=have out=trans(drop=_:);
by sex question;
var yes_no;
run;

proc transpose data=trans out=want(drop=_:) prefix=Question_;
by sex;
id question;
var col:;
run;

The first step assumes that HAVE is sorted by sex question, which is the case for your sample data.

View solution in original post

5 REPLIES 5
NicoM
Obsidian | Level 7

Have you tried using PROC TRANSPOSE? You can accomplish what you want using that procedure. You can find some basic examples in this document: 060-2009: Learn the Basics of PROC TRANSPOSE (sas.com).

Kurt_Bremser
Super User
data have;
input question sex $ yes_no;
datalines;
1 male  1
1 male  1
1 male  0
1 male  0
1 male  0
1 male  0
1 male  0
2 male  1
2 male  1
2 male  1
2 male  0
2 male  0
2 male  0
2 male  0
3 male  1
3 male  1
3 male  1
3 male  1
3 male  1
3 male  0
;

data want;
if 0 then set have (keep=sex); /* sets attributes */
call missing(sex,question_1,question_2,question_3);
merge
  have (
    rename=(yes_no=question_1)
    where=(question = 1)
  )
  have (
    rename=(yes_no=question_2)
    where=(question = 2)
  )
  have (
    rename=(yes_no=question_3)
    where=(question = 3)
  )
;
drop question;
run;

Please note how example data is presented in a data step with datalines; this removes any doubts about content or variable attributes. Do so in the future. It's basic courtesy towards your helpers.

andreas_lds
Jade | Level 19

The table you want looks like a report and not like a dataset. Are you sure, that you need the wide structure for further processing?

FreelanceReinh
Jade | Level 19

Hello @jonatan_velarde,

 

Here is an example illustrating NicoM's suggestion:

proc transpose data=have out=trans(drop=_:);
by sex question;
var yes_no;
run;

proc transpose data=trans out=want(drop=_:) prefix=Question_;
by sex;
id question;
var col:;
run;

The first step assumes that HAVE is sorted by sex question, which is the case for your sample data.

ballardw
Super User

If this is at all related to this thread https://communities.sas.com/t5/SAS-Programming/Convert-data-set-from-summary-to-full-data/m-p/728565 where you convert summary data into an arbitrary ordered set by categorical variables then the target set is invalid for any sort of analysis. You are creating an artificial association between the values of the question variables.

 

Really.

If you do not have the individual level data to start with then your entire approach is going to be so flawed I can't describe it.

 

There are some things that might be possible with something like Proc Mixed and similar procedures but your "created from summary to individual level data" is misrepresenting the information.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 781 views
  • 5 likes
  • 6 in conversation