- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I’m having trouble with transpose truncating my data. In my regular code I get this error:
ERROR: The ID value "'WHAT DO YOU FIND MOST CHALLENGIN'n" occurs twice in the same BY group.
I don’t get the error with this sample code but the questions in each column do get truncated. It could be that the questions in my regular code are much longer (around 100 characters).
data test1;
infile datalines delimiter=',';
length ques_txt $200;
input ques_txt $ person_id $ response $ uniqueid;
datalines;
This question is nonsense about how long the question is,22,No,1
This question is more nonsense about nothing,22,Yes,2
This question is still more nonsense,22,Maybe,3
This question is nonsense about how long the question is,65,Yes,4
This question is more nonsense about nothing,65,Who knows,5
This question is still more nonsense,65,Si,6
This question is nonsense about how long the question is,44,No,7
This question is more nonsense about nothing,44,Yes,8
This question is still more nonsense,44,Not on your life,9
;
run;
proc sort data=test1 nodupkey out=test2;by person_id ques_txt ;run;
proc transpose data=test2 out=test3 (drop=_name_ _label_);
by person_id ;
id ques_txt;
var response;
run;
I should have added that the output should look like this. You can see that the questions in the column headers where cut off at 32 characters whereas these in the sample data are around 45 to 50 characters but the production questions are around 100 characters.
person_id This question is more nonsense a This question is nonsense about This question is still more nons
22 Yes No Maybe
44 Yes No Not on y
65 Who know Yes Si
Thanks
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The ID statement in PROC TRANSPOSE lets you tell it what variable to use to NAME the variables it creates. Variables in SAS can only by up to 32 characters long.
If you want to attach those long descriptive text strings to the variables use the IDLABEL statement in PROC TRANSPOSE to use those strings as the LABEL to be attached to the variable.
data test1;
infile datalines delimiter=',';
length person_id $8 ques_txt $200 response $30 uniqueid 8;
input ques_txt person_id response uniqueid;
datalines;
This question is nonsense about how long the question is,22,No,1
This question is more nonsense about nothing,22,Yes,2
This question is still more nonsense,22,Maybe,3
This question is nonsense about how long the question is,65,Yes,4
This question is more nonsense about nothing,65,Who knows,5
This question is still more nonsense,65,Si,6
This question is nonsense about how long the question is,44,No,7
This question is more nonsense about nothing,44,Yes,8
This question is still more nonsense,44,Not on your life,9
;
proc sort data=test1;
by ques_txt;
run;
data test2 ;
set test1;
by ques_txt;
ques_num + first.ques_txt;
run;
proc sort data=test2 nodupkey out=test3;
by person_id ques_num;
run;
proc transpose data=test3 prefix=ques out=test4 (drop=_name_ _label_);
by person_id ;
id ques_num;
idlabel ques_txt;
var response;
run;
proc print data=test4;
run;
proc print data=test4 label;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The ID statement in PROC TRANSPOSE lets you tell it what variable to use to NAME the variables it creates. Variables in SAS can only by up to 32 characters long.
If you want to attach those long descriptive text strings to the variables use the IDLABEL statement in PROC TRANSPOSE to use those strings as the LABEL to be attached to the variable.
data test1;
infile datalines delimiter=',';
length person_id $8 ques_txt $200 response $30 uniqueid 8;
input ques_txt person_id response uniqueid;
datalines;
This question is nonsense about how long the question is,22,No,1
This question is more nonsense about nothing,22,Yes,2
This question is still more nonsense,22,Maybe,3
This question is nonsense about how long the question is,65,Yes,4
This question is more nonsense about nothing,65,Who knows,5
This question is still more nonsense,65,Si,6
This question is nonsense about how long the question is,44,No,7
This question is more nonsense about nothing,44,Yes,8
This question is still more nonsense,44,Not on your life,9
;
proc sort data=test1;
by ques_txt;
run;
data test2 ;
set test1;
by ques_txt;
ques_num + first.ques_txt;
run;
proc sort data=test2 nodupkey out=test3;
by person_id ques_num;
run;
proc transpose data=test3 prefix=ques out=test4 (drop=_name_ _label_);
by person_id ;
id ques_num;
idlabel ques_txt;
var response;
run;
proc print data=test4;
run;
proc print data=test4 label;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
That works. Thanks so much Tom.