topic Re: How to Add multiple Columns with dynamic names to a dataset in SAS Enterprise Guide

How to Add multiple Columns with dynamic names to a dataset

Kuda35 — Wed, 04 May 2016 06:31:53 GMT

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;

Re: How to Add multiple Columns with dynamic names to a dataset

Ksharp — Wed, 04 May 2016 07:33:04 GMT

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;

Re: How to Add multiple Columns with dynamic names to a dataset

RW9 — Wed, 04 May 2016 08:11:57 GMT

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