Please try: ods listing close; data test;   attrib avar avar2 format=$2000.;   avar=repeat("A",2000);   avar2=repeat("B",2000);   output; run; ods tagsets.excelxp file="s:\temp\rob\xyz.xls" style=statistical; proc report data=test nowd;      column avar avar2;      define avar / "Label";      define avar2 / "Label"; run; ods tagsets.excelxp close; Does this work in a clean SAS session? ... View more

Not sure what you are doing there. You should be using ods with output procedures, SQL is not an output procedure.  Try: ods tagsets.excelxp file="...\xyz.xls" style=statistical; proc report data=have nowd;      column variable_print;      define variable_print / "Label"; run; ods tagsets.excelxp close; ... View more

I would personally put your if statements in quotes and trim them e.g.: %if %trim(&data_type)="csv".  Whats happening is the if statement is not true and hence none of the other code operates. As Tom previously mentioned however, you don't need to do a lot of that code.  If you look at a datastep with an infile statement you can have multiple delimiters (there are examples on here of that).  The other code as well can also be trimmed.  Am leaving for the day, however if its still open I add some code. ... View more

Yes, excellent suggestion.  A few runs of the below two steps shows first using about 0.08 seconds less than the substr (e.g. 0.23 vs 0.31). data x;   attrib res format=$20.;   do i=1 to 2000000;   res="assfsa";   substr(res,1,1)=upcase(first(res));   end; run; data y;   attrib res format=$20.;   do i=1 to 2000000;   res="assfsa";   substr(res,1,1)=upcase(substr(res,1,1));   end; run; ... View more

Just to note, you can modify a character in place, without have to concatenate data back together again, though you won't notice any time diff without lots of rows data x;   attrib res format=$20.;   res="assfsa";   substr(res,1,1)=upcase(substr(res,1,1)); run; ... View more

Well, its not pretty, but gives a result: data subscriptions;    infile datalines;    format date_from date_to format=date9.;    input @01 id 1.          @03 magazine $4.          @08 date_from date9.          @18 date_to date9.;    datalines;                      1 mag1 01jan2014 15mar2014 1 mag2 20feb2014 12apr2014 1 mag3 20feb2014 12apr2014 1 mag4 25mar2014 16may2014 2 mag1 01jan2014 01jul2014 2 mag2 01feb2014 01apr2014 ; run; proc sql noprint;   select  min(date_from)   into    :min_date   from    WORK.SUBSCRIPTIONS;   select  max(date_to)   into    :max_date   from    WORK.SUBSCRIPTIONS; quit; data template (keep=id days);   attrib id format=best. days format=date9.;   do j=1 to 2;     do i=&min_date. to &max_date.;       id=j; days=i; output;     end;   end; run; data have;   set subscriptions;   attrib days format=date9.;   do i=date_from to date_to;     days=i; output;   end; run; proc sql;   create table working as   select  TEMP.ID,           TEMP.DAYS,           catx('/',A.MAGAZINE,B.MAGAZINE,C.MAGAZINE,D.MAGAZINE) as MAG   from    TEMPLATE TEMP   left join (select * from HAVE where MAGAZINE="mag1") A   on      TEMP.ID=A.ID   and     TEMP.DAYS=A.DAYS   left join (select * from HAVE where MAGAZINE="mag2") B   on      TEMP.ID=B.ID   and     TEMP.DAYS=B.DAYS   left join (select * from HAVE where MAGAZINE="mag3") C   on      TEMP.ID=C.ID   and     TEMP.DAYS=C.DAYS   left join (select * from HAVE where MAGAZINE="mag4") D   on      TEMP.ID=D.ID   and     TEMP.DAYS=D.DAYS; quit; data working2 (drop=lstmag);   set working;   attrib start_date format=date9.;   by id;   retain start_date lstmag;   if first.id then do;     start_date=days;     lstmag=mag;   end;   else do;     if lstmag ne mag then do;       start_date=days;       lstmag=mag;     end;   end; run; proc sort data=working2 out=working3;   by descending id descending days; run; data working3;   set working3;   attrib end_date format=date9.;   retain end_date lstmag;   if lstmag ne mag then do;     flag="Y";     end_date=days;     lstmag=mag;   end; run; proc sort data=working3 out=final;   by id mag;   where flag="Y" and mag ne ""; run; data final (keep=id magazine start_date end_date);   set final;   i=1;   do while (scan(mag,i,'/') ne "");     magazine=scan(mag,i,'/'); output;     i=i+1;   end; run; ... View more

But you would still have subscription per monthly basis, so day is still irrelevant.  You would need to slot dates to monthly sections, e.g. you may consider one of these scenarios: Jan if 01Jan2014 - 31Jan2014, Feb if 01Feb - 29Feb etc. Or maybe you need different windows, e.g. 20Dec2014 - 20Jan is considered the month of Jan. Either way break it down into sections, e.g. month, or if weekly then weekly, or if you do days then days, the process I pretty similar, just modify the array. ... View more