topic Re: How to fill the sequential alphabet letter to survey questions in SAS Programming

How to fill the sequential alphabet letter to survey questions

daisy6 — Tue, 01 May 2018 15:06:30 GMT

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!

Re: How to fill the sequential alphabet letter to survey questions

novinosrin — Tue, 01 May 2018 15:26:34 GMT

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;

Re: How to fill the sequential alphabet letter to survey questions

ballardw — Tue, 01 May 2018 15:49:26 GMT

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.

Re: How to fill the sequential alphabet letter to survey questions

FloT — Tue, 01 May 2018 15:58:55 GMT

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!