Desktop productivity for business analysts and programmers

Calculating members

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Calculating members

Hi Need to calculate members for each month

Have

Mem_IDstart_dateend_date
123201401201412
234201208201501
345201211201403
456201306201412
567201401201406

Want

Year_MonthCount_of_members
2014015
2014025
2014035
2014044
2014054
2014064
2014073

Thanks in advance


Accepted Solutions
Solution
‎07-09-2015 08:44 AM
Super User
Posts: 9,875

Re: Calculating members

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;

View solution in original post


All Replies
Valued Guide
Posts: 858

Re: Calculating members

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;

Super User
Posts: 11,134

Re: Calculating members

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;

Contributor
Posts: 21

Re: Calculating members

Hi Ballardw,

Dates are currently in Character

Respected Advisor
Posts: 3,156

Re: Calculating members

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;

Contributor
Posts: 21

Re: Calculating members

Thanks Hai.Kuo

Sure From next I will input data in ready format

Solution
‎07-09-2015 08:44 AM
Super User
Posts: 9,875

Re: Calculating members

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;

Contributor
Posts: 21

Re: Calculating members

Thanks Xia Smiley Happy

It Worked Perfectly

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 531 views
  • 4 likes
  • 5 in conversation