The SAS Output Delivery System and reporting techniques

Generate excel output with different colors .

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Generate excel output with different colors .

Hi Every one . can any one help me out in this issue .

i have to import excel sheet into data set (large data set),

every week same excel sheet get updated .(increases to number of rows or columns )

i need to find out updated part in excel sheet

yes  compare with previous data set i can get that .

My Question :?

Here i need to generate excel sheet all rows and columns with updated part (rows ) in yellow color .


Accepted Solutions
Solution
‎03-26-2015 04:38 AM
New Contributor
Posts: 2

Re: Generate excel output with different colors .

Hi plz find the below answer for diffrentiation of data with excel output using ods output .

ODS TAGSETS.EXCELXP PATH="D:\GSKBIO\USERS\SSP\VARDHAN\Histo pCR" FILE="&out..xls" STYLE=sansPrinter;

PROC REPORT DATA= sashelp.classNOWD;

COLUMN _ALL_ ;

COLUMN zebrarows ;

  DEFINE zebrarows / COMPUTED NOPRINT;

  COMPUTE zebrarows ;

    IF sex= "M"  THEN

    CALL DEFINE (_ROW_ ,'STYLE', 'STYLE=[BACKGROUND=#e0ffff]' );

  ENDCOMP;

RUN;

ODS TAGSETS.ExcelXP CLOSE;

yes its work for 9.2 and above versions only .

this code  generates data as i expected , thanks to every one .

View solution in original post


All Replies
Super User
Super User
Posts: 7,720

Re: Generate excel output with different colors .

Hi,

Well firstly, the problem you are facing is that you are using Excel,  Its a simple as that really.  Over the last 20-30 years or so some very clever people have been developing tools specifically designed for tasks like these, they are called databases.  Now what you are asking to do is to have Excel do the same as this, even though it was not designed for such a thing.  So, you first need to decide where the processing for this would take place, you can either keep a SAS import of the spreadsheet, import any new data, check updates, then export the data out again, or use VBA macros in Excel.  Either way you will be basically writing code to emulate the various functions of a database.  I.e. you mention that there will be updates.  What does this entail, new records, deleted records, updated records.  What identifiers are in place so that you can identify unique records.  What audit trail functionality would need to be in place.  What user access is necessary.  If you are in my industry and this is used for critical data then you need to read up on CFR-part 11 compliance etc.

My suggestion, drop Excel as its useless.  Start out by identifying what you want to acheive, what is available as a tool to over the task.  Speak to your IT about current implementations etc.  Unless you go through proper channels of SDLC, implementation, and using correct guidelines, all you will end up with is an unstable, unvalidated tool which will cause you more headaches along the line.

Solution
‎03-26-2015 04:38 AM
New Contributor
Posts: 2

Re: Generate excel output with different colors .

Hi plz find the below answer for diffrentiation of data with excel output using ods output .

ODS TAGSETS.EXCELXP PATH="D:\GSKBIO\USERS\SSP\VARDHAN\Histo pCR" FILE="&out..xls" STYLE=sansPrinter;

PROC REPORT DATA= sashelp.classNOWD;

COLUMN _ALL_ ;

COLUMN zebrarows ;

  DEFINE zebrarows / COMPUTED NOPRINT;

  COMPUTE zebrarows ;

    IF sex= "M"  THEN

    CALL DEFINE (_ROW_ ,'STYLE', 'STYLE=[BACKGROUND=#e0ffff]' );

  ENDCOMP;

RUN;

ODS TAGSETS.ExcelXP CLOSE;

yes its work for 9.2 and above versions only .

this code  generates data as i expected , thanks to every one .

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 524 views
  • 0 likes
  • 2 in conversation