BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
twildone
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

9 REPLIES 9
ballardw
Super User

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.

SASKiwi
PROC Star

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.

twildone
Pyrite | Level 9


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?

SASKiwi
PROC Star

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

Ksharp
Super User

the simplest way is add BLANKS before it .

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

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

twildone
Pyrite | Level 9

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.

Ksharp
Super User

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

Cynthia_sas
SAS Super FREQ

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;

twildone
Pyrite | Level 9

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.

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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