The SAS Output Delivery System and reporting techniques

re: Proc Report

Accepted Solution Solved
Reply
Regular Contributor
Posts: 229
Accepted Solution

re: Proc Report

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;


Accepted Solutions
Solution
‎05-23-2015 09:59 AM
SAS Super FREQ
Posts: 8,868

Re: re: Proc Report

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:

excelxp_example.png

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;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: re: Proc Report

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.

Super User
Posts: 3,260

Re: re: Proc Report

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.

Regular Contributor
Posts: 229

Re: re: Proc Report


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?

Super User
Posts: 3,260

Re: re: Proc Report

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.);

Super User
Posts: 10,044

Re: re: Proc Report

the simplest way is add BLANKS before it .

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

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

Regular Contributor
Posts: 229

Re: re: Proc Report

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.

Super User
Posts: 10,044

Re: re: Proc Report

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

Solution
‎05-23-2015 09:59 AM
SAS Super FREQ
Posts: 8,868

Re: re: Proc Report

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:

excelxp_example.png

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;

Regular Contributor
Posts: 229

Re: re: Proc Report

Posted in reply to Cynthia_sas

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 769 views
  • 0 likes
  • 5 in conversation