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
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.
Post your example data as a DATA step with DATALINES, not as a picture which we can't insert in our SAS program editors.
@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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.