Hi Need to calculate members for each month
Have
Mem_ID | start_date | end_date |
---|---|---|
123 | 201401 | 201412 |
234 | 201208 | 201501 |
345 | 201211 | 201403 |
456 | 201306 | 201412 |
567 | 201401 | 201406 |
Want
Year_Month | Count_of_members |
---|---|
201401 | 5 |
201402 | 5 |
201403 | 5 |
201404 | 4 |
201405 | 4 |
201406 | 4 |
201407 | 3 |
Thanks in advance
data have;
infile cards dsd;
informat Mem_ID $3. start_date end_date yymmn6.;
format Mem_ID $3. start_date end_date yymmn6.;
input Mem_ID$ start_date end_date;
cards;
123,201401,201412
234,201208,201501
345,201211,201403
456,201306,201412
567,201401,201406
;
run;
data temp;
set have;
do i=0 to intck('month',start_date,end_date);
date=intnx('month',start_date,i);
output;
end;
drop i start_date end_date;
format date yymmn.;
run;
proc sql;
create table want as
select date,count(distinct Mem_ID) as count_mem
from temp
group by date;
quit;
Your want table doesn't come from the have table but I think I have a solution for you.
data have;
infile cards dsd;
informat Mem_ID $3. start_date end_date yymmn6.;
format Mem_ID $3. start_date end_date yymmn6.;
input Mem_ID$ start_date end_date;
cards;
123,201401,201412
234,201208,201501
345,201211,201403
456,201306,201412
567,201401,201406
;
proc sql;
create table want as
select start_date as Year_Month,count(mem_id) as Count_of_members
from have
group by start_date
order by start_date;
Are your "dates" currently character or numeric?
The example below reads dates as character and create SAS date valued variables so we can create one record per month per member, and then count the months with members. WARNING: if your Start and end dates are reversed this will not work, or if the month parts are invalid.
data have;
input Mem_ID start_date $ end_date $;
/* need SAS date valued variables to increment correctly*/
sdate = input(start_date,yymmn6.);
edate = input(end_date,yymmn6.);
format sdate edate yymmn6.;
datalines;
123 201401 201412
234 201208 201501
345 201211 201403
456 201306 201412
567 201401 201406
;
run;
data temp;
set have;
format testdate yymmn6.;
testdate=sdate;
do while (testdate le edate);
output;
testdate = intnx('month',testdate,1,'same');
end;
run;
proc summary data=temp;
class testdate;
var testdate;
output out=want(drop=_type_ _freq_) n=Member_count;
run;
Hi Ballardw,
Dates are currently in Character
Next time we 'd appreciate your input data is ready in the format of a data step. This is what I think you are after:
data have1;
input
Mem_ID $ /
start_date yymmn6. / end_date yymmn6. @@;
format start_date end_date yymmn6.;
cards;
123
201401
201412
234
201208
201501
345
201211
201403
456
201306
201412
567
201401
201406
;
data have2;
do i=0 to 11;
ym=intnx('month','01jan2014'd,i,'s');
output;
end;
format ym yymmn6.;
drop i;
run;
proc sql;
create table want as
select ym, (select count(*) from have1 where a.ym between start_date and end_date) as ct
from have2 a;
quit;
Thanks Hai.Kuo
Sure From next I will input data in ready format
data have;
infile cards dsd;
informat Mem_ID $3. start_date end_date yymmn6.;
format Mem_ID $3. start_date end_date yymmn6.;
input Mem_ID$ start_date end_date;
cards;
123,201401,201412
234,201208,201501
345,201211,201403
456,201306,201412
567,201401,201406
;
run;
data temp;
set have;
do i=0 to intck('month',start_date,end_date);
date=intnx('month',start_date,i);
output;
end;
drop i start_date end_date;
format date yymmn.;
run;
proc sql;
create table want as
select date,count(distinct Mem_ID) as count_mem
from temp
group by date;
quit;
Thanks Xia
It Worked Perfectly
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.