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!
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!
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;
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.
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.