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;
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;
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.
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.
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?
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.);
the simplest way is add BLANKS before it .
if name='January' then name=' January';
if name='February' then name=' February';
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.
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
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.