topic Re: Adding variables within ID to create new variable in SAS Programming

Adding variables within ID to create new variable

ercksh8 — Wed, 21 Apr 2021 00:54:29 GMT

data scale; set cleaned; by ID;
	IF a = 1 then score1 = 1;
	if a = 2 | 3 then score1 = 2;
	if a >= 4 & a <= 6 then score1 = 3;
	IF b = 1 then score2 = 1;
	if b = 2 | 3 then score2 = 2;
	if b >= 4 & b <= 6 then score2 = 3;
	IF c = 1 then score3 = 1;
	if c = 2 | 3 then score3 = 2;
	if c >= 4 & c <= 6 then score3 = 3;
	total_score = score + score + score;

	if d = 1 then act1 = 2; else act1 = 1;
	if e = 1 then act2 = 2; else act2 = 1;
	if f = 1 then act3 = 2; else act3 = 1;
	if g = 1 then act4 = 2; else act4 = 1;
	total_act = act1 + act2 + act3 + act4;
run;

This is the code that I have been trying. I know for sure there are IDs in which a, b, and c are all 1 but there are no cases where total_score = 3. I would like to know if there is a way to fix my code so that I can get my desired output.

Re: Adding variables within ID to create new variable

Astounding — Wed, 21 Apr 2021 02:24:01 GMT

This statement is a problem:

if a = 2 | 3 then score1 = 2;

True, | means "or". But it is unrelated to the value of A. It's equivalent to:

if (3) or (a=2) then score1=2;

And the condition 3 is always true. A simple replacement will work:

if a in (2, 3) then score1 = 2;

Re: Adding variables within ID to create new variable

Tom — Wed, 21 Apr 2021 02:24:58 GMT

There are two obvious mistakes.

First is you probably want to add ELSE statements in there are the appropriate places.

Second you have places where you are treating integer constants as boolean expressions. So this expression is always true since whether or not A is equal to 2 does not matter as 3 is always treated is TRUE. SAS will treat any number other than zero (or a missing value) as TRUE.

if a = 2 | 3 then score1 = 2;

Also add some parentheses into your expressions to make sure you know the order they will be evaluated. It will also make it easier to understand if you used the OR and AND keywords instead of trying to save a couple of characters of typing by using (potentially confusing) | and & symbols instead.

if (a = 1) then score1 = 1;
else if (a = 2) or (a = 3) then score1 = 2;
else if (a >= 4) and (a <= 6) then score1 = 3;

Re: Adding variables within ID to create new variable

andreas_lds — Wed, 21 Apr 2021 05:19:15 GMT

Seems to be a great job for a format and some arrays:

data have;
   length a b c d e f g 8;
   
   array scores[3] a b c;
   array acts[4] d--g;
   
   do _n_ = 1 to 10;
      do _i = 1 to dim(scores);  
         scores[_i] = rand('Integer', 1, 6);
      end;
      
      do _i = 1 to dim(acts);
         acts[_i] = rand('Integer', 0, 1);
      end;
      
      output;
   end;

   drop _i;
run;

proc format;
   invalue ScoreFmt
      1 = 1
      2, 3 = 2
      4 - 6 = 3
   ;
run;


data want;
   set have;
      
   array toScores[3] a b c;
   array scores[3];
   
   array toActs[4] d--g;
   array acts[4];
   
   do _i = 1 to dim(toScores);
      scores[_i] = input(put(toScores[_i], 1.), ScoreFmt.);
   end;
   
   total_scores = sum(of scores[*]);
   
   do _i = 1 to dim(toActs);
      acts[_i] = (toActs[_i] = 1) + 1;
   end;
   
   total_acts = sum(of acts[*]);
run;

Re: Adding variables within ID to create new variable

Kurt_Bremser — Wed, 21 Apr 2021 06:01:00 GMT

total_score = score + score + score;

Unless score is present in the incoming dataset, this statement will create missing values throughout. I guess you wanted

total_score = score1 + score2 + score3;

or, even better

total_score = sum(score1,score2,score3);

Re: Adding variables within ID to create new variable

Patrick — Wed, 21 Apr 2021 09:06:42 GMT

Similar approach as suggested by @andreas_lds but without arrays to not "overload" you.

proc format;
  invalue score
    1 =1
    2-3=2
    4-6=3
    other=.
  ;
run;

data scale;
  set cleaned;
  by ID;
  score1=input(a,score.);
  score2=input(b,score.);
  score3=input(c,score.);
  total_score=sum(score1,score2,score3);

  if d = 1 then act1 = 2;  else act1 = 1;
  if e = 1 then act2 = 2;  else act2 = 1;
  if f = 1 then act3 = 2;  else act3 = 1;
  if g = 1 then act4 = 2;  else act4 = 1;
  total_act = sum(act1,act2,act3,act4);
run;