Hi! I have a variable with 30+ sequences that I need to now put into 3 categories. If the variable contains >=4 of a 0 or 1 they are assigned value A and B respecitvely. All others=C. THe sequence combination is not relevant, just the number of a certain digit in the value. I'm not sure which function i should use here. Help is appreciated!
Example of data:
data want;
set have;
datalines;
00000 * would be new_Value 'A';
00001 * would be new_Value 'A';
00010 * would be new_Value 'A';
00011 * would be new_Value 'C';
00100 * would be new_Value 'A';
01000 * would be new_Value 'A';
01100 * would be new_Value 'C';
11000 * would be new_Value 'C';
10111 * would be new_Value 'B;
11110 * would be new_Value 'B';
11111 * would be new_Value 'B';
;
data have;
input seq $;
datalines;
00000
00001
00010
00011
00100
01000
01100
11000
10111
11110
11111
;
data want;
set have;
count_zero=0;
count_one=0;
category='C';
do i=1 to length(seq);
if substr(seq,i,1)='0' then count_zero+1;
if substr(seq,i,1)='1' then count_one+1;
end;
if count_zero>=4 then category='A';
if count_one>=4 then category='B';
run;
Read each 0/1 into its own variable, then use the sum function. If the sum> X then assign A/B, otherwise C.
data have;
input seq $;
datalines;
00000
00001
00010
00011
00100
01000
01100
11000
10111
11110
11111
;
data want;
set have;
count_zero=0;
count_one=0;
category='C';
do i=1 to length(seq);
if substr(seq,i,1)='0' then count_zero+1;
if substr(seq,i,1)='1' then count_one+1;
end;
if count_zero>=4 then category='A';
if count_one>=4 then category='B';
run;
And just to go around the bend a bit, here is solution using a format. The data step builds a set to build the format.
data junk;
length start label $ 5;
FMTNAME = 'MYCATEGORIES';
type='C';
do a=0,1;
do b=0,1;
do c=0,1;
do d=0,1;
do e=0,1;
start=cats(a,b,c,d,e);
sum=sum(a,b,c,d,e);
select (sum);
when (0,1) Label='A';
when (4,5) Label='B';
otherwise Label='C';
end;
output;
end;
end;
end;
end;
end;
run;
proc format LIBRARY=work cntlin=junk;run;
The you could possibly use the format instead of creating a new variable:
Proc freq data=have;
Tables myvar;
Format myvar $mycategories. ;
run;
Or if you really want a variable
Newvar = put(myvar, $mycategories.);
This wouldn't be the easiest solution BUT does have the potential of allowing an "other" clause added to the junk data set to address things like the value being '0100'. What if the variable doesn't have all 5 columns? What do you want to assign? Or possibly even more interesting a value of '01 00'? You could add one line to the junk dataset that would handle values that were too short, had other than 0 adn 1 characters and potentially too long by having a formatted value of blank or 'Z' or something else.
Loop through is definitely not the most efficient...
Here's the COUNTC example:
data want;
set have;
N1=countc(seq, '1');
N0=countc(seq, '0');
if N0 >= 4 then cat='A';
else if N0 >= 4 then cat='B';
else cat='C';
run;
Just a small typo:
else if N1 >= 4 then cat='B';
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!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: