05-04-2016 02:31 AM
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.
/* Exposure months*/
format mmdate ddmmyy10.;
%Do i = 2010 %to 2010;
%Do j = 1 %to 2;
k = 1;
%let eom=intnx( 'month', mmdate,0,'end');
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;
05-04-2016 03:33 AM
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;
05-04-2016 04:11 AM
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