BookmarkSubscribeRSS Feed
Kuda35
Calcite | Level 5

I have a table with membership data on customers with From_date(start) and to_date(end), which can be over several years. with this data I need to calculate the member of days in each month that the person is a member.

 

To do this, I will would like to add a new column for each month 'EXPMMYYYY' to the dataset  and calculatte the membership days for each month. Having each months membership set out this way with help with some calculations later in the process. I would like this to be dynamic as this code will form part of a process that will be repeated on a monthly basis.  As such, I I need  the looping code that allows me to add  the 'EXPMMYYYY' columns dynamically to the dataset.

 

To give better idea of the input and expected output data, please see attached file with both. 

 

The code I have so far is given below. Although this creates the additional columns  there are blank or zero. 

 

%macro A;

data worklib.ExposureC;
set worklib.ExposureB;

/* Exposure months*/
format mmdate ddmmyy10.;

%Do i = 2010 %to 2010;
%Do j = 1 %to 2;
k = 1;

%let mmdate=mdy(j,k,i);
%put mmdate;
%let eom=intnx( 'month', mmdate,0,'end');
%let numdays=day(eom);
output;

EXP_&i&j = 0;

x = 0;
if mmdate>= FROM_DATE and mmdate<= TO_DATE then
x = numdays;

if year(mmdate)= year(FROM_DATE) and month(FROM_DATE)= month(FROM_DATE) then
x = numdays - day(From_date);

if year(mmdate)= year(TO_DATE) and month(mmdate)= month(TO_DATE) then
x = day(TO_DATE);

%let EXP_&i&j = x;
output;

%End;
%End;
%mend A;
%A;
run;

2 REPLIES 2
Ksharp
Super User
data have;
infile cards expandtabs truncover;
input CONT_NO	(FROM_DATE	TO_DATE) (: ddmmyy10.);
format FROM_DATE	TO_DATE ddmmyy10.;
cards;
1	15/07/2010	01/09/2010
2	15/07/2010	01/01/2011
3	15/08/2010	01/01/2011
4	15/09/2010	01/01/2011
5	15/10/2010	01/01/2011
6	15/11/2010	01/01/2011
7	01/01/2010	01/01/2011
8	01/02/2010	01/01/2011
9	01/03/2010	01/09/2010
10	01/04/2010	01/01/2011
11	01/05/2010	01/01/2011
12	01/06/2010	01/01/2011
13	01/07/2010	01/01/2011
14	20/08/2010	01/01/2011
15	01/09/2010	01/01/2011
16	01/10/2010	01/01/2011
17	01/11/2010	01/01/2011
18	01/12/2010	01/01/2011
19	01/01/2010	01/01/2011
20	01/01/2010	01/01/2011
;
run;
data temp;
 set have;
 length id $ 20;
 do date=FROM_DATE to TO_DATE-1;
  id=cats('EXP',put(date,yymmn6.));
  output;
 end;
run;
proc summary data=temp ;
 by CONT_NO FROM_DATE TO_DATE id;
 output out=temp1;
run;
proc transpose data=temp1 out=temp2(drop=_name_);
 by CONT_NO FROM_DATE TO_DATE;
 id id;
 var _freq_;
run;
proc stdize data=temp2 out=temp3 reponly missing=0;
 var EXP:;
run;

proc sql noprint;
select name into : list separated by ' '
 from dictionary.columns
  where libname='WORK' and memname='TEMP3' and name like 'EXP%'
   order by input(compress(name,,'kd'),best.) ;
quit;
data want;
 retain  CONT_NO FROM_DATE TO_DATE &list;
 set temp3;
run;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Simple answer is don't.  You will end up with years * 12 columns.  Its not the best way to work, create a normalised structure, so one ROW for each data point, with a minimal fixed structure.  You will find all of your programming on that dataset is then far easier and more maintainable.  If you needa report which is transposed then transpose at that point (although no-one will ever look at a report with hundreds of columns).

<other vars>  MONYR    COUNT

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1825 views
  • 2 likes
  • 3 in conversation