topic Count Number of Entries present rowise in New SAS User

Count Number of Entries present rowise

Kirito1 — Thu, 18 May 2023 05:46:16 GMT

I have a prepared data which have VPA(Virtual Payment Address), and and Number of transactions they have made datewise from 1 APRIL, 2023 till 30APRIL2023. Something like the below Screenshot.

Now, I want to count number of days that VPA was active. I want to introduce a new column which contains number of days they active and that count should be stored in a new column.

Thanks in Advance for all the contributors

Re: Count Number of Entries present rowise

Kurt_Bremser — Thu, 18 May 2023 06:06:28 GMT

Post your example data as a DATA step with DATALINES, not as a picture which we can't insert in our SAS program editors.

Re: Count Number of Entries present rowise

PaigeMiller — Thu, 18 May 2023 10:01:33 GMT

@Kirito1 wrote:

Now, I want to count number of days that VPA was active.

What does "VPA was active" mean? How would we determine this from the data?

Re: Count Number of Entries present rowise

yabwon — Thu, 18 May 2023 10:42:29 GMT

1) The form of table you have is a textbook example of "bad data practice". If I were you I would keep your data in "long" form:

data have;
input VPA $  date date9. value;
format date date9.;
cards;
ABC1 1apr2023 .
ABC1 2apr2023 2
ABC1 3apr2023 3
ABC1 4apr2023 .
ABC1 5apr2023 5
ABC2 1apr2023 .
ABC2 2apr2023 .
ABC2 3apr2023 3
ABC2 4apr2023 4
ABC2 5apr2023 .
ABC3 1apr2023 1
ABC3 2apr2023 .
ABC3 3apr2023 .
ABC3 4apr2023 4
ABC3 5apr2023 5
;
run;

with such "structure-less" data you could do the calculation like this:

data want1;
  set have;
  where value;
  by VPA;

  Number_of_days + 1;

  if last.VPA then
    do;
      output;
      Number_of_days = 0;
    end;
  keep VPA Number_of_days;
run;
proc print;
run;

2) But since you have data in this "bad" format:

options validvarname=ANY;
proc transpose data=have out=have_BAD(drop=_name_);
  by VPA;
  id date;
  var value;
run;
proc print;
run;

you could do it like this using variables arrays:

data want2;
  set have_BAD;
  array d '1apr2023'n--'5apr2023'n;

  do over d;
    Number_of_days + d>.z;
  end;
run;
proc print;
run;

Bart

[EDIT:]

P.S. Just to be clear variable arrays are very practical and useful tools. And it is "good programming practice" to learn how to work with them.