topic Re: Create variable that counts progressively how many times variable repeated in SAS Programming

Create variable that counts progressively how many times variable repeated

dht115 — Fri, 31 Mar 2023 16:17:23 GMT

Hi,

I need to find how many times account_id is repeating along with department.

I tried to use first. procedure for this problem. I was able to find how many times account_id is repeating; however, I was not able to get the desire value for department.

Also, do we have proc sql procedure to do something similar in SAS?

data have;
input Account_Id Dept Salary Emp_Status $ Projects $ Rating;
cards;
111         123  7000  Perm       A        5
111         123  6000  Perm       B        4
111         123  7000  Perm       C        5
222         124  6000  Perm       A        5
333         125  7000  Perm       B        4
333         125  7000  Perm       C        5
;
run;

proc sort data=have;
by account_id dept;
run;

data want ;
set have;
by account_id;
if first.account_id then do;
flag = 0;
if first.dept then flag_1 = 0;
end;
flag_1 + 1;

flag + 1;
retain flag_1;
retain flag;
run;

Re: Create variable that counts progressively how many times variable repeated

Kurt_Bremser — Fri, 31 Mar 2023 16:22:48 GMT

You get FIRST. variables only for variables included in the BY statement, so you must have dept there. Add the NOTSORTED option if "smaller" values can follow "larger" values.

Re: Create variable that counts progressively how many times variable repeated

PaigeMiller — Fri, 31 Mar 2023 16:23:32 GMT

Its usually unhelpful to show us the wrong answer, and not show us the correct answer. So show us the answer you want for this data.

If you need values that are incremented on each row subject to some conditions, SQL is not the tool.

Re: Create variable that counts progressively how many times variable repeated

Tom — Fri, 31 Mar 2023 16:29:28 GMT

I don't understand what you are counting. Can you share the expected output for the given input?

Also I don't understand how your data is organized. Are departments defined within accounts? Or are accounts defined within departments?

If you want to count how many times the combination of account and department changes and number each record within those combinations you could use something like this:

data want;
  set have;
  by Account_Id Dept ;
  department_no + first.dept;
  record_no+1;
  if first.dept then record_no=1;
run;

Result

       Account_                       Emp_                           department_    record_
Obs       Id       Dept    Salary    Status    Projects    Rating         no           no

 1        111       123     7000      Perm        A           5           1            1
 2        111       123     6000      Perm        B           4           1            2
 3        111       123     7000      Perm        C           5           1            3
 4        222       124     6000      Perm        A           5           2            1
 5        333       125     7000      Perm        B           4           3            1
 6        333       125     7000      Perm        C           5           3            2