topic re: Proc Report in ODS and Base Reporting

re: Proc Report

twildone — Tue, 19 May 2015 17:46:52 GMT

Hi....I am having some difficulities using Proc Report to create a report. I have the dataset sorted and in the everything is exactly how I would like it to appear in a report. However, when the report is created, the some of the rows change their order based on 'Month'. I tried a few things and can't seem to resolve this problem. Does anyone know what I need to do to keep this from happening. Thanks.

PROC REPORT DATA=TALIN NOWD SPANROWS
STYLE(header)={background=#33cccc font_face=Calibri
font_size=12pt borderwidth=1px bordercolor=black just=CENTER}
STYLE(COLUMN)={background=white font_face=Calibri font_size=10pt borderwidth=1px
bordercolor=black just=CENTER};

COLUMN CalendarYear Quarter Month 'Unique Clients'n 'Unique Authors'n;

DEFINE CalendarYear / ORDER 'Calendar Year';
DEFINE Quarter / ORDER 'Quarter';
DEFINE Month / ORDER 'Month';
DEFINE 'Unique Clients'n / ORDER ORDER=DATA 'Unique Clients';
DEFINE 'Unique Authors'n / ORDER ORDER=DATA 'Unique Authors';
RUN;
QUIT;

Re: re: Proc Report

ballardw — Tue, 19 May 2015 19:26:45 GMT

If you have presorted the data you might want to try ORDER=Data to maintain that sort order for your variables.

You don't show but I suspect you have month names and are getting results in "April" "August" order.

Re: re: Proc Report

SASKiwi — Tue, 19 May 2015 19:59:06 GMT

If Month is a character column then in could be ordering by month name not date order. You would be best to change month to a SAS date.

Re: re: Proc Report

twildone — Thu, 21 May 2015 00:07:29 GMT

Hi...yes I have Month as a character with full name such as January, February, etc....how would I changed this variable to a SAS Date?

Re: re: Proc Report

SASKiwi — Thu, 21 May 2015 02:26:52 GMT

You would also need to use the year, then something like this might work (assuming your CalendarYear is numeric):

SAS_Date = input('01' !! substr(Month,1,3) !! put(CalendarYear,4.), date9.);

Re: re: Proc Report

Ksharp — Fri, 22 May 2015 12:34:17 GMT

the simplest way is add BLANKS before it .

if name='January' then name=' January';

if name='February' then name=' February';

Re: re: Proc Report

twildone — Sat, 23 May 2015 00:26:34 GMT

Hi...I tried your suggestion of inserting blanks before the actual months and didn't seem to work. I ended up creating another column with the Month Number and sorted by this column and this seems to work but now I have both the Months and Month Number included in my table and excel file. If I exclude the Month Number from being displayed in the table and excel file, the Output Table that is created in SAS looks fine but when it exports it to Excel, some of the months are re-arranged.

Re: re: Proc Report

Ksharp — Sat, 23 May 2015 06:34:18 GMT

Mine Looks good.

data  have;
length name $ 20;
 name='January'; output;
 name='February';output;
run;

data temp;
 set have;
 length n $ 20;
if name='January' then n='     January';
if name='February' then n='  February';
drop name;
run;

ods tagsets.excelxp file='c:\temp\x.xls';
proc report data=temp nowd;
column n;
define n/order;
run;
ods tagsets.excelxp close;

Xia Keshan

Re: re: Proc Report

Cynthia_sas — Sat, 23 May 2015 13:59:43 GMT

Hi:

Padding with blanks is not always a good technique to rely on. It doesn't work reliably in all destinations. If the OP is running at least 9.3 with TAGSETS.EXCELXP, then either of these examples should work -- either with Month number as a NOPRINT or just using a Date variable formatted with MONNAME. as shown below:

Code is at bottom of post.

Cynthia

data start;

length name $ 20;

infile datalines dlm=',' dsd;

input name $ val;

return;

datalines;

January,10

February,30

March,40

April,70

run;

data final;

set start;

length n $ 20;

if name='January' then n=1;

if name='February' then n=2;

if name = 'March' then n = 3;

if name = 'April' then n = 4;

dateval = mdy(n,01,2015);

run;

ods tagsets.excelxp file='c:\temp\xx.xml' style=htmlblue

options(sheet_interval='none');

proc report data=final nowd;

column n name val;

define n / order noprint;

define name / order;

rbreak after / summarize;

compute before _page_;

line '1) Using Month Number as NOPRINT';

endcomp;

run;

proc report data=final nowd;

column dateval val;

define dateval / order order=internal f=monname.

style(column)={just=l};

rbreak after /summarize;

compute before _page_;

line '2) Using Date Variable for Ordering';

endcomp;

run;

ods tagsets.excelxp close;

Re: re: Proc Report

twildone — Mon, 25 May 2015 14:37:33 GMT

Hi Cynthia...Thank you for your help and suggestions. I ended up having to use the Month Number Approach as I tried the Dateval Approach and kept getting an error message that the Dateval needs to be character format. The Month Number approach works perfect...thanks once again for your help.