Calculating members

[rkolupoti9001]

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

[Steelers_In_DC]

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;

[ballardw]

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;

[rkolupoti9001]

Hi Ballardw,

Dates are currently in Character

[Haikuo]

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;

[rkolupoti9001]

Thanks Hai.Kuo

Sure From next I will input data in ready format

[Ksharp · Accepted solution]

Code: Program

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;

[rkolupoti9001]

Thanks Xia

It Worked Perfectly