topic Re: create a variable based on values across multiple columns in SAS Programming

create a variable based on values across multiple columns

panda — Mon, 26 Aug 2019 19:04:03 GMT

My data looks like this:

data dat;
   input id ind01 ind02 ind03 ind04 ind05 ind06;
   datalines;
   1 0 0 0 0 4 0
   2 0 0 1 0 3 0 
   3 4 4 4 4 4 4
   4 1 2 3 2 2 2
   5 1 3 0 0 2 0 
   6 1 2 3 4 2 2
   ;
run;

I want to create a variable to stratify the rows into three groups:

group A: have either 0 or 4 for all variables ind01-ind06;

group B: have any of 1 or 2 or 3, for all variables in01-ind06;

group C: change from (0 or 4) to (1 or 2 or 3) (or vice versa) for all variables ind01-ind06;

The ideal dataset looks like this:

data want;
   input id ind01 ind02 ind03 ind04 ind05 ind06 type $;
   datalines;
   1 0 0 0 0 4 0 A
   2 0 0 1 0 3 0 C
   3 4 4 4 4 4 4 A
   4 1 2 3 2 2 2 B
   5 1 3 0 0 2 0 C
   6 1 2 3 4 2 2 C
   ;
run;

I am not sure how to create the variable since it needs tracking changes across multiple columns. Any suggestions? Thanks!!

Re: create a variable based on values across multiple columns

novinosrin — Mon, 26 Aug 2019 19:13:35 GMT



data dat;
   input id ind01 ind02 ind03 ind04 ind05 ind06;
   datalines;
   1 0 0 0 0 4 0
   2 0 0 1 0 3 0 
   3 4 4 4 4 4 4
   4 1 2 3 2 2 2
   5 1 3 0 0 2 0 
   6 1 2 3 4 2 2
   ;
run;
/*Not sure of the group=C*/
data want;
set dat;
array t ind:;
if countc(cats(of t(*)),'04')=dim(t) then group='A';
else if countc(cats(of t(*)),'123')=dim(t) then group='B';
else if countc(cats(of t(*)),'04123')=dim(t) then group='C';
run;

Re: create a variable based on values across multiple columns

PaigeMiller — Mon, 26 Aug 2019 19:21:32 GMT

data want;
input id ind01 ind02 ind03 ind04 ind05 ind06;
array ind ind01-ind06;
zero=0;
four=0;
do i=1 to dim(ind);
zero=zero+(ind(i)=0);
four=four+(ind(i)=4);
end;
if zero+four=6 then group='A';
else if zero+four=0 then group='B';
else group='C';
drop i;
datalines;
1 0 0 0 0 4 0
2 0 0 1 0 3 0 
3 4 4 4 4 4 4
4 1 2 3 2 2 2
5 1 3 0 0 2 0
6 1 2 3 4 2 2
;
run;