Hi:   You've posted versions of this question in a few different places: https://communities.sas.com/thread/38919 and https://communities.sas.com/message/145004#145004 and https://communities.sas.com/message/144987#144987   It's hard to understand what you want. One example of PROC REPORT code was already posted to do the color coding. You have another example of doing data manipulation. It seems like your data example and your description of what you want changes in every post. In a previous posting, someone asked you how you were creating your variables, since "2012Q1" is not a valid variable name in SAS -- are you using EG and is your form of reference '2012Q1'n???   Do you need an output dataset (you have some suggested code for that) or do you need a report (there's been code posted for that)? If you need a report, what is your destination of choice? In one of your other postings, you showed some indenting and color coding that would not be available in LISTING output (for example). However, this report that you've posted looks very similar to the picture you posted in the GRAPH forum and it resembles the report with the CU values that you've shown in another forum posting. Do you need the red color coding that you showed in your other post?   PROC REPORT can calculate columns for you. See the example below; I used some fake data, based on SASHELP.PRDSALE, since I'm still not clear on your data or what you mean by "dynamic" variables. But this program is just a variation on the previous PROC REPORT that I posted -- it shows having the _2012Y and the _YOY columns computed by PROC REPORT. And it still does color coding, as shown in your screenshot. cynthia data fakedata;   set sashelp.prdsale;    region_nm = region;    cu_nm = substr(product,1,3);    ** make some fake numbers;    _2011Y = sum(actual *.75, predict*.86);    _2012Q1 = actual/4;    _2012Q2 = predict / 4;    _2012Q3 = actual / 3.33;    _2012Q4 = predict / 4.71;    _2013Q1 = actual / 2.55;    _2013Q2 = predict / 4.21;    ** get different values for CU_NM for each REGION_NM;    if region_nm = 'EAST' then do;       if cu_nm in ('SOF','TAB') then output;    end;    else do;       if cu_nm in ('DES','CHA') then output;    end;    format _numeric_ comma12.2; run;       ods html file='c:\temp\doreport2.html' style=sasweb;     title 'Sample Report';     proc report data=fakedata nowd split='#'          style(report)={rules=none frame=void cellspacing=0}          style(header)={background=verydarkblue color=white                         font_weight=bold};       column region_nm cu_nm              _2011Y _2012Q1 _2012Q2               _2012Q3 _2012Q4 _2012Y _YOY               _2013Q1 _2013Q2 ;       define region_nm / group order=data ' ';       define cu_nm / group order=data ' ';       define _2011Y / sum '2011Y#REQ'              style(header)={background=cxeeeeee color=black}              style(column)={background=cxeeeeee};       define _2012Q1 /sum '2012Q1#REQ' ;       define _2012Q2  /sum '2012Q2#REQ'   ;       define _2012Q3 /sum '2012Q3#REQ+F' ;       define _2012Q4 /sum '2012Q4# F ' ;       define _2012Y /computed '2012Y#REQ+F' f=comma12.2              style(header)={background=cxeeeeee color=black}              style(column)={background=cxeeeeee};       define _YOY /computed '2011/2012#YoY' f=comma12.2              style(header)={background=cxbbbbbb color=black}              style(column)={background=cxbbbbbb};       define _2013Q1 /sum '2013Q1# F ' ;       define _2013Q2 /sum '2013Q2# F' ;       compute _2012Y;         _2012Y = sum(_2012Q1.sum, _2012Q2.sum, _2012Q3.sum, _2012Q4.sum);       endcomp;       compute _YOY;         _YOY = _2011Y.sum - _2012Y;       endcomp;       break after region_nm / summarize;       compute after region_nm;         line ' ';       endcomp;     run; ods _all_ close; ... View more

Thanks！ ... View more

Hi:   I'm not sure what you mean when you say that you have "dynamic" variables. You could mean that you have SAS Macro variables, you could mean that you prompt the user for the variables they want and then you have a program that selects the variable accordingly, you could mean dynamic in that the variables change in every run, you could mean that you are using ACROSS variables in PROC REPORT. I hardcoded some variable names to show that the kind of highlighting that you wanted to do was simple with PROC REPORT and to illustrate that this isn't really a graph question, in my opinion. I don't know what structure your report data is in, whether you have all the columns you need or whether you need to calculate them in a DATA step or a PROC REPORT step. I created a report that showed how to do the highlighting you wanted. Getting your data into the form used by my PROC REPORT is an exercise in mapping the variables you have to the "fake" names I chose in my program.   I could have called my variables FRED, ETHEL, LUCY, RICKY, WOMBAT, KOALA, WIDGET, GIDGET, WOCKET and WACKET. It really doesn't matter, I called them something similar to what you showed in your screenshot just to make it easier to understand how and where the highlighting was happening.   Let's assume that I had designed PROC REPORT like this, with "generic" column names:     column col1 col2 col3 col4 col5 col6 col7 col8 col9 col10; Then before, my PROC REPORT step, let's say that I take my data and for the purposes of creating a final dataset, I have something like this where I rename my "dynamic" variables to have the names that PROC REPORT is expecting: data report_final;    set dyn_data(rename=(vdp=col1 _2011Y=col2 _2012Q1=col3 _2012Q2=col4              _2012Q3=col5 _2012Q4=col6  _2012Y=col7 _YOY=col8               _2013Q1=col9 _2013Q2=col10 )); run; ** PROC REPORT step that uses the "generic" column names; You asked how to keep the "these columns" in the "same output", but I don't know what you mean by "these columns". What PROC REPORT code have you tried? What is the structure of your input data? Are you doing this as part of a stored process? Is this something in an EG project? Where is the "dynamic" data coming from? What is your destination of choice for the output -- what kind of output do you want table or report or graph? Since you posted this in the GRAPH forum, where does the graph component come into play? cynthia Also, it looks like you have posted a version of this question in another forum. In your other posting, it looks like you want a dataset, not a report from PROC REPORT and not a graph. https://communities.sas.com/message/144987#144987 ... View more

Dear Other think, you must request to the administrator of your system if some changes appear on the management of disk, activities of the system, If you use a share NFS request more information about the traffic network, You can also request if the management of the disk where the SASWORK is define change, the status of disk, the defragmentation of the disk etc... You must have a look of the system. regards ... View more

hi ... using Linlin's data ... proc summary data=rydata nway; class id; output out=lastid (drop=_:) idgroup(max(num) out(num name)=); run; proc sort data=lastid; by num; run; ... View more

Thus is working as intended.  Copying a view to a view just copies the associated commands; no data are moved.  That's why there is such a time difference.  RTFM. ... View more

Hi:   First, my usual curmudgeonly rant: When you use ODS techniques to create output that can be opened with Excel, you are NOT, NOT, NOT creating true, binary .XLS files and you are NOT, NOT, NOT technically doing an EXPORT. Only PROC EXPORT or the LIBNAME statement for Excel can create true, Excel proprietary format files. When you use ODS, you are either creating CSV, or HTML or XML ASCII text files that Excel knows how to open and render. So what you create in your stored process is just ASCII text, not proprietary Excel binary file structure.   When you use ODS TAGSETS.EXCELXP, you are creating Spreadsheet Markup Language XML, as defined in Office 2003 (and NOT HTML). So, to comment on your questions: 1) use a different style template in your ODS invocation (something like style=sasweb or style=htmlblue) Do be aware, though that some client applications (like WRS or PPT) might not use this style. Or, use style=overrides in your PROC PRINT code, such as proc print data=xxx.yyy style(data)={background=white};   2) see the first comments above -- first you are creating XML, not HTML and second, it is not a true binary Excel file when you use ODS. If you don't like the message that you get from Excel when it opens the file, then change the file extension to .XML and not .XLS That may not stop Excel from complaining when it opens the XML file, but depending on how you are running your stored process, you have little choice with how Excel treats the XML.    3) This is a "feature" of Excel -- it likes to ignore your SAS format. Generally, however, I find in SAS 9.3 that if I use a format statement, like: format mydatevar date9.; that TAGSETS.EXCELXP sends the correct Microsoft format to Excel. If you are finding this is not the case, you may need to explicitly send a Microsoft format to Excel using the TAGATTR style attribute. If you search the forums, there have been many previous postings about using TAGATTR with TAGSETS.EXCELXP. I am not on a system where I can work with stored processes. If you have specific questions, you might want to work with Tech Support on this, because they can look at the client application you are using to execute the stored process and help you with stored process specifics. Also, there are many previous postings on using TAGSETS.EXCELXP in the Stored Process Forum postings. cynthia ... View more

exactly so won't the table have to be read multiple times with the different conditions? Try the left join and let me know how much time that takes. ... View more

So, just to clarify, you have a stored process with cascading prompts and the prompts are populated from an information map? Can you provide more details about how you set this up? ... View more

If you are updating / appending a small number of records in your large dataset then using MODIFY may be the most efficient technique. And if you haven't done so already, I would recommend creating an index on COL1 in your master dataset.  Below are 2 examples of updating the master dataset (indexed) using MODIFY, the 2nd example is likely to be fastest if your transaction dataset is small.  There's a good paper on using MODIFY at http://www2.sas.com/proceedings/sugi31/264-31.pdf data have (index=(col1)); input (id col1 col2) ($); cards; 5 id2 desc3 1 id1 desc1 ; run; data new; input (id col1 col2) ($); cards; 4 id3 desc4 3 id1 desc2 ; run; /* example 1 */ data have ; modify have new (rename=(id=id_new col2=col2_new)); by col1;   select (_iorc_);   when (%sysrc(_sok)) do;     col2=col2_new;   replace;   id=id_new;   output;   end;   when (%sysrc(_dsenmr)) do;   id=id_new;   col2=col2_new;   output;   _error_=0;   end; otherwise end; run ; /* example 2 */ data have; set new (rename=(id=id_new col2=col2_new)); modify have key=col1;   select (_iorc_);   when (%sysrc(_sok)) do;     col2=col2_new;   replace;   id=id_new;   output;   end;   when (%sysrc(_dsenom)) do;   id=id_new;   col2=col2_new;   output;   _error_=0;   end; otherwise end; run; Message was edited by: Keith Timms Sorry, just realised that this method only works properly where the values for COL1 are unique in the master dataset.  I'll have a look to see if I can get it working where duplicate values exist. ... View more

Hi: I see a few possible things to correct. Generally, when you use %STPBEGIN, you do NOT use the STPSRV_HEADER function. Or, if you are using the STPSRV_HEADER function, then you use FILE=_WEBOUT and do NOT use %STPBEGIN/%STPEND. There have been many previous postings about creating TAGSETS.EXCELXP output. And quite a few of the postings have included complete and working code. In addition there are some Tech Support notes about using STPSRV_HEADER: http://support.sas.com/kb/12/994.html (although examples 4 and 5 show using %stpbegin/%stpend, do note that those are PDF and RTF examples and are not immediately relevant to what you are trying to do). At the very least, I would recommend that you try this: 1) REMOVE the %STPBEGIN/%STPEND from your stored process code since you have the ODS statements. By default, without an override to &_ODSDEST, the output created is probably either SASReport XML or HTML. Since you are using the STPSRV_HEAER function, then you must be submitting this SP through the Information Delivery Portal or the Stored Process Web App. In which case, the alternate method of using ODS is OK but (see #2) 2) Usually, if you are using an ODS invocation statement, you also use FILE=_WEBOUT or BODY=_WEBOUT. So, if you are going to remove %STPBEGIN/%STPEND and you are going to leave in the call to STPSRV_HEADER, then I would recommend that you alter your code to be:    data _null_; rc= stpsrv_header('Content-type', 'application/vnd.ms-excel'); rc= stpsrv_header('Content-disposition', 'attachment; filename= muliple sheets.xls'); run;       ods tagsets.ExcelXP file=_webout options(sheet_name="REP1"); ** proc report 1; ods tagsets.ExcelXP options(sheet_name="REP2"); ** proc report 2; ods tagsets.ExcelXP close; You posted almost the same question, only about using TAGSETS.CSVALL to a different forum. Which result type of output do you really want? Have you searched for examples of using TAGSETS.EXCELXP in a stored process? There are many complete code examples posted to this forum. How are you submitting this stored process? Using the IDP or using the Stored Process Web App? Once you use STPSRV_HEADER, you are essentially creating a stored process that can only be run using client applications that can receive "streaming" output. So, for example, your posted stored process using STPSRV_HEADER would not work in Web Report Studio or in Powerpoint; nor is it likely to work in the Add-in for Microsoft Office using Excel. By default, a stored process submitted from within the Add-in for Microsoft Office using Excel can only "receive" SASReport XML, HTML or CSV results. One other reason for not using both %STPBEGIN -and- STPSRV_HEADER is that %STPBEGIN issues one kind of content-type header and your STPSRV_HEADER issues another kind. If %STPBEGIN sent a content-type for text/html, then your "stream" would be received as an HTML stream, which would go into one worksheet and your %STPSRV_HEADER would essentially be ignored. If you opened a track with Tech Support on this issue, they could help you capture your SAS log from this run and they could examine your log for errors or warning messages. Until you correct your stored process code, you will continue to get unreliable results when you run your stored process. cynthia ... View more

Hi:   This looks like a stored process. The way a stored process works is to populate 1 sheet of 1 workbook. If you ran the stored process from within the SAS Add-in for Microsoft Office, you would get the results of both PROC REPORT steps in 1 worksheet in 1 workbook.   When you do use the SAS Add-in for Microsoft Office, you do have an interactive window that  prompts you whether you want the results to go into an existing sheet or a new sheet or a new workbook. What caused you to try sheet_name as a suboption for CSVALL destination? It is not a valid option for TAGSETS.CSVALL destination. You can prove this to yourself (and see the valid suboptions) by using the options(doc='Help') suboption and then looking in the log for the valid suboptions for the CSVALL destination. For your convenience, the valid suboptions for CSVALL, using the SAS 9.3 install are: ============================================================================== The CSV Tagset Help Text. This Tagset/Destination creates output in comma separated value format. Numbers, Currency and percentages are correctly detected and show as numeric values. Dollar signs, commas and percentages are stripped from numeric values by default. ============================================================================== These are the options supported by this tagset. Sample usage: ods csv options(doc='Quick'); ods csv options(currency_as_number='yes' percentage_as_number='yes' delimiter=';'); Doc:  No default value.      Help: Displays introductory text and options.      Quick: Displays available options. Delimiter:   Default Value ','      Sets the delimiter for the values.  Comma is the default.  Semi-colon is      a popular setting for european sites. currency_as_number:   Default Value 'No'      If 'Yes' currency values will not be quoted.      The currency values are stripped of punctuation and currency symbols      so they can be used as a number. percentage_as_number:   Default Value 'No'      If 'Yes' percentage values will not be quoted.      The percentages are stripped of punctuation and the percent sign      so they can be used as a number. Currency_symbol:   Default Value '$'      Used for detection of currency formats and for      removing those symbols so excel will like them.      Will be deprecated in a future release when it is      no longer needed. Decimal_separator:   Default Value '.'      The character used for the decimal point.      Will be deprecated in a future release when it is no longer needed. Prepend_Equals:   Default Value 'no'      Put an equal sign in front of quoted number values.      This only works in conjunction with quote_by_type. quote_by_type:   Default Value 'no'      Put values based on the type, not based on what the regex match for number. Table_Headers:   Default Value 'yes'      If no, skip the header section of all tables. Thousands_separator:   Default Value ','      The character used for indicating thousands in numeric values.      Used for removing those symbols from numerics so excel will like them.      Will be deprecated in a future release when it is no longer needed. Quoted_columns:   Default Value ''      A list of column numbers that indicate which values should be quoted      ie. Quoted_columns="123" Bylines:   Default Value: No      If yes bylines will be printed Titles:   Default Value: No      If yes titles and footnotes will be printed Notes:   Default Value: No      If yes Note, Warning, Error, and Fatal notes will be printed Proc_Titles:   Default Value: No      If yes titles generated by the procedures will be printed ============================================================================== As you can see, there is no "sheet_name" suboption for TAGSETS.CSVALL. Therefore, when you used this suboption, it was ignored. Even if you create a CSV file using the CSVALL destination in SAS, without using the BI Platform or stored processes, you would get the results of both PROC REPORT steps in one worksheet -- this is how Excel would open the CSV file. So, the bottom line is that TAGSETS.CSVALL will never give you a multi-sheet workbook.   You might have read something about TAGSETS.EXCELXP -- this is a destination which DOES create multi-sheet workbooks and DOES have a sheet_name suboption. There are many postings in the Stored Process Forum about using TAGSETS.EXCELXP with a stored process on the BI Platform to create a multi-sheet workbook. When the stored process is requested using the Information Delivery Portal or the Stored Process Web Application, then you can get multi-sheet workbooks.   I'd suggest that you investigate the use of TAGSETS.EXCELXP. This destination creates Microsoft-compliant Spreadsheet Markup Language XML, as defined for Office 2003. Microsoft designed this set of XML markup tags to describe a workbook. You will need Office 2003 or higher to correctly render the XML. Another suggestion would be to post your stored process questions to the Stored Process Forum, or at the very least to search the forums for more information about creating multi-sheet workbooks using a stored process. If you continue to have problems with your stored process, then you might also wish to open a track with Tech Support. They can help you figure out how to best write a stored process given your requirements and your BI configuration. cynthia ... View more

Even though it looks like a non-Windows system, the path to the STP directory is awfully long (121 characters): (/opt/app/SAS/work/SAS_work87D2000036D6_esessux0015/SAS_work8E07000036D6_esessux0015/7e539000-ab52-11e1-8001-313430333831/) I know for a fact that copying SAS software depot into Myfiles directory or into Desktop, causes PC SAS installation process to fail. I don't know how your STP directory gets assigned and whether this is relevant at all in your case, but if you have any input into this assignment, try shortening this path. ... View more

Hi:   PROC REPORT can only show what is in the dataset specified by the DATA= option. If the dataset (let's call it WORK.NEW) has zero observations, then you are probably seeing messages like this: NOTE: No observations in data set WORK.NEW. NOTE: No observations in input data set.   If you need to find out whether there are observations in the data set so you can change your program logic to insert an observation with all missing values, then you will probably need to look to more advanced programming techniques or using SAS Macro programming to craft a solution. cynthia ... View more

Try to use regular FTP client to check your account name and make sure your password is not expired. Regards, Haikuo ... View more