How to fill the sequential alphabet letter to survey questions

[daisy6]

Hi SAS experts,

Here is another problem I try to solve.

V1                    V2                         V3

survey_1          age                      Under 21

survey_1          age                       21 - 25

survey_1          age                      26 - 30

survey_1          age                      31 - 40

survey_1          age                      41 - 50

survey_1          age                      50+

survey_2          Education            Some High School

survey_2          Education            High School Graduate

survey_2          Education            Some College

.......

I need to create V4  then just like:

V1                    V2                         V3                                        V4

survey_1          age                      Under 21                               A

survey_1          age                       21 - 25                                  B

survey_1          age                      26 - 30                                   C

survey_1          age                      31 - 40                                   D

survey_1          age                      41 - 50                                   E

survey_1          age                      50+                                        F

survey_2          Education            Some High School                A

survey_2          Education            High School Graduate          B

survey_2          Education            Some College                       C

.......

How can I get V4 which is what I want. Thank you!

[novinosrin]

data have;
input V1 : $15.                   V2 :$20.                     V3 & :$30.;
datalines;
survey_1          age                      Under 21
survey_1          age                       21 - 25
survey_1          age                      26 - 30
survey_1          age                      31 - 40
survey_1          age                      41 - 50
survey_1          age                      50+
survey_2          Education            Some High School
survey_2          Education            High School Graduate
survey_2          Education            Some College
;


data want;
set have;
by v1 v2;
retain _t ;
if first.V2 then do;_t=65; v4=byte(_t);end;
else do;_t+1; v4=byte(_t);end;
drop _t;
run;

[ballardw]

Another way would be to create a custom format as long as none of the values of V3 ever get a different V4 value.

Something like

proc format library=work;
value $ToLetter
'Under 21', 'Some High School'='A'
'21 - 25', 'High School Graduate'='B'

<contiue the obvious pattern>
;
run;

And then either use the format with V3 or

data want;
   set have;
   v4 = put(v3,$ToLetter.);
run;

Though I do have to say the use of those letters isn't immediately obvious.

[FloT · Accepted solution]

Hello,

I would do this way:

/* Copy your test dataset */
data test;
	infile datalines delimiter="|";
	length v1 $10 v2 $20 v3 $50;
	input v1 $ v2 $ v3 $;
	datalines;
	survey_1|age|Under 21
	survey_1|age|21 - 25
	survey_1|age|26 - 30
	survey_1|age|31 - 40
	survey_1|age|41 - 50
	survey_1|age|50+
	survey_2|Education|Some High School
	survey_2|Education|High School Graduate
	survey_2|Education|Some College
	;
run;

/* Adding colomn v4 */
%let letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

data test1;
	set test;
	retain v4_tmp;
	by v1;
	if first.v1 then v4_tmp = 0;
	v4_tmp = v4_tmp + 1;
	v4 = substr(&letters,v4_tmp,1);
	drop v4_tmp;
run;

I hope this helps!