The SAS Output Delivery System and reporting techniques

Export procedure to use for formatting column headers with 42 variables

Reply
Regular Contributor
Posts: 155

Export procedure to use for formatting column headers with 42 variables

Hi,

 

I have 42 variables as in the attached sheet.I am creating tabs in the same sheet from where I am reading the input.The Sheet1 contains the input data and I am creating about 20 tabs but i showed just 2 tabs.I want the column headers to be in the same format as in sheet1.

I am exporting using proc export and the output column header looks as shown in sheet2 &sheet3.

 

I know proc report can be used to format the column header but I need to define all the variables.Instead of that is there any better way to achieve the output without defining all the output variables.

 

What's the best way to get the column headers in the same format as in sheet1

 

Any help would be appreciated.

Grand Advisor
Posts: 17,389

Re: Export procedure to use for formatting column headers with 42 variables

I'm just seeing a list of variable names without formatting. Perhaps include an image instead, I won't download XLSX files and many users won't. 

 

You actually don't need to specify all variables in proc report, but you do need to define the style for the header. 

SAS Super FREQ
Posts: 8,720

Re: Export procedure to use for formatting column headers with 42 variables

Hi:
  Reeza is correct, you do not need to list all the variables, IF you want the variable label to be used as the column header -- PROC REPORT does this by default. However, if the variables need to be in a certain order or if you do not have labels or if the variables need a certain format, or a certain usage (like GROUP or ORDER), or a grand total or sub totals, then other statements might be needed. Without a usable example of your data, it is hard to comment.

However, this should serve to illustrate one possible approach -- this lists all the character variables first followed by all the numeric variables and changes the header color to blue and illustrates how to change a few things too (like font).

controlHeaders.png

cynthia

Regular Contributor
Posts: 155

Re: Export procedure to use for formatting column headers with 42 variables

Thanks Cynthia & Reeza for your response.I am observing very strange behavior

 

ods excel file="\\corp\...\test1.xls" options(sheet_name="Rsch");

proc report data=Rsch
style(header)={background=white foreground=cx0000ff width=1in};
column ACCOUNT_NUM BORROWERS_NAME BANK_1 ORIGINATION_DATE DAYS_PAST_DUE LEGAL_STATUS RT_CURR_BAL RT_ACCT_TYPE AP_INTEREST_RATE RT_PROC_TYPE
RT_ACCT_STAT RT_DATE_STAT_SET RT_TFR_STAT RT_CURR_DUE_AMT CF_CU_CUST_INFO_CD CF_CB_CUST_INFO_CD CFF_CUST_NO CO_REF_NUM CO_YEAR CO_MAKE
CO_MAKE1 CO_MODEL RT_ACCR_STAT RT_EXT_USER_FLD RT_CTL2 RT_STATE_CD RT_RENG_CD FIELD_AGENT RT_POFF_AMT RT_GL_KEY1 RT_EXT_USER_FLD1
BK_BANK_ATTY_CODE BK_PETITION1 BK_PETITION2 BK_PETITION3 BK_PETITION4 BK_PETITION5 BK_PETITION_FILED_DATE BK_PETIT_NTC_RECD_DATE
BK_DOCKET_NUMBER BK_DISCHARGE_DATE RT_POOL_CD RT_USER_GRP2 RT_SE_COMMIT_DATE SE_REPRCHSE_DATE SE_SALE_DATE;
run;
ods excel close;

 

The problem I am seeing is it's printing the columns until Y fine but after that the columns are not being printed. I can't show this in an image so I am uploading xls. I can't show the data as it's confidential

 

 

 

Regular Contributor
Posts: 155

Re: Export procedure to use for formatting column headers with 42 variables

Is there any limitation on column statement? As far as I know it's not.When I checked the columns after RT_CTL2 and try to print them they printed fine. I don't know why it's not printing after RT_CTL2 column

Grand Advisor
Posts: 17,389

Re: Export procedure to use for formatting column headers with 42 variables

What version of SAS do you have?

I believe in the earlier versions of SAS 9.4 (TS1M1/M2?) there was a bug that limitied it to 26 columns.

 

 

Super User
Super User
Posts: 6,364

Re: Export procedure to use for formatting column headers with 42 variables

Does the ODS EXECL statement even support XLS format? I thought you had to use XLSX format.

SAS Super FREQ
Posts: 8,720

Re: Export procedure to use for formatting column headers with 42 variables

Correct. ODS EXCEL only creates .XLSX "new" Office format files.
cynthia
Regular Contributor
Posts: 155

Re: Export procedure to use for formatting column headers with 42 variables

I am using SAS9.4

 

I mean when i used it created xls files .So that's why this bug is occuring as i am using xls format? I tried it using xlsx format as well same issue

 

When i tried with ods excelcp tagset it worked fine but curious why it didn't work with ods excel

SAS Super FREQ
Posts: 8,720

Re: Export procedure to use for formatting column headers with 42 variables

[ Edited ]

Hi:

ODS TAGSETS.EXCELXP is actually creating an XML file -- Spreadsheet Markup Language XML from the Office 2003 standard. When you name the file as .XLS, you are just "fooling" the Windows Registry into launching Excel to open the XML instead of launching a browser or some other XML application.

When you use ODS EXCEL, you are moving into the Office 2007 world, where Excel, Word and PPT all got new file extensions: XLSX, DOCX and PPTX. These NEW extensions are NOT the same as the 2003 XML and they are not binary XLS files. So it would be wrong and inappropriate to name an ODS EXCEL file with the .XLS extension. It MUST be given a .XLSX file extension. If you use .XLS, then Excel is not happy.

However, if you try this:
ods excel file='c:\temp\class.xlsx' style=htmlblue;
   proc print data=sashelp.class;
   run;
ods excel close;

Then it SHOULD work. If this works and your code does NOT work, using ODS EXCEL and the .XLSX extension, then you have an issue for Tech Support.

cynthia

Ask a Question
Discussion stats
  • 9 replies
  • 164 views
  • 0 likes
  • 4 in conversation