Another way is to get the sum of non-missing value and merge it back to subtract it . Here is DOW skill. data have; input C1 :$ C2 :$ N1; cards; R1   . 10 R1  S1   1 R1  S2   2 R2    . 20 R2  S5   4 R2  S9   1 R3     . 9 R4 S4   1 ; run; data want; sum=0; do until(last.c1);   set have;   by c1;   if not missing(c2) then sum+n1; end;   do until(last.c1);   set have;   by c1;   if  missing(c2) then n1=n1-sum;   output; end; drop sum; run; Xia Keshan ... View more

Hi:   In addition to all of the other suggestions, I have a different one. When you are using %stpbegin, you do NOT need to repeat your ODS invocation statements: %let _ODSDEST=TAGSETS.csvall; %stpbegin; ods tagsets.csvall options(sheet_name="Report1"); Proc Print data=Report1 noobs width=full style(data)={background=white};       format COL1 COL2 yymmd9.;    var COL1 COL2 COL3 COL4; run; ods tagsets.csvall close; %stpend; In addition, your invocation statement is unnecessary because there is no "sheet_name" suboption for ODS CSV or ODS CSVALL, so your "extra" statement will have no impact on your output.  So, aside from all the other issues, you are doing something that is not possible with the CSV destination. cynthia ... View more

Download the latest version and check if that changes things. Download the code from here and run it, you don't need install privileges or admin rights. Page: Base SAS: ODS MARKUP Download Tagset link: ExcelXP ... View more

Hi sunilreddy, We have a track still opened for months on a similar issue. - a SAS 9.3 (TS1/M1) Metadata Server on Linux x64  is unable to authenticate against an LDAP server through SSL (LDAPS) using a chained Verisign Certificate. it works fine using a self-signed certificate but fails with a chained Verisign. SAS tech support has tried to replicate our issue but we disagree on the minimal requirements and, unfortunately, this is still unresolved despite upgrading the ssl SAS code up to 9.4. In our case, the LDAPS certificate should be declared with SSLCALISTLOC SAS sytem option. Be aware that SAS awaits the SSL certificate to be read as a plain text file (ASCII); I am note sure it can retrieve the certificate from a *.dlb key store. Since I'm not familiar with SAS 9.1 on Solaris, check the documentation. I'll be curious to know if you can open the LDAPS call at all. Please, keep us informed. Ronan ... View more

Hi Peter, I've tested your code as below but still not giving expected data out1; input PROD $ CUST $ NF PER_DT DATE9.; FORMAT PER_DT DATE9.; DATALINES4; 130 1214 24 01APR2013 130 1214 144 01MAY2013 130 1214 57 01JUN2013 130 1214 123 01JUL2013 130 1214 0 01AUG2013 130 1214 2 01SEP2013 130 1214 3 01OCT2013 130 1214 3 01NOV2013 130 1214 3 01DEC2013 ;;;; RUN; %let periods = 3 ; * or longer when wanted; %let forecast_periods = 10 ; DATA rolling ; ARRAY rolls(&periods) _temporary_ ; NFe = MEAN( OF rolls(*) ) ; * now insert latest data into rolls array until end of data; SET  out1 end= lastdatarow; Row+1; Pointer = MOD( Row, &periods ) +1 ; rolls( Pointer ) = nf ; *release data row and finish, unless end of input ; Output ; If not lastdatarow then return ; Do _n_ = row to ( row + &forecast_periods ) ; NFe = MEAN( OF rolls(*) ) ; Output; per_dt = intnx( 'month', per_dt, 1 ) ; Pointer = MOD( _n_, &periods ) +1 ; Rolls(pointer) = NFe ; End ; STOP ; RUN ; ... View more

Hi Haikuo, Your code is also meeting the requirement...Good one... ... View more

if the objective is to control data loading so that records with invalid data go into a rejects pile, and the good stuff is stored separately, then little additional programming is needed, because SAS supports convenient variable lists on INPUT statements. As with much of "old SAS" not a lot of people seem to remember input ( a--b) (?) ; will input all variables starting with A through to variable B (in the order defined to the compiler) with default informats and suppress invalid data messages, but still setting _ERROR_ to 1 if any informat fails to recognise its data.. The style      ( var list) (format list and format modifiers) allows the ? modifier to apply to all these variables. The data step structure would then work like data errors( compress=yes)  wanted( drop= record ) ; length A 8 ; informat var1 informat_for_var1  var2 $informat_for_var2  var3 ..............varZ informat_for_varZ ; length B $1  record $1000 ; infile 'where-ever\your_data' truncover {other infile options} ; input (var1 -- varz) (?) @ ; record = _infile_ ; if _error_ then  output errors ; else output wanted ; drop A B ; _error_ = 0 ; run ; although I didn't use A and B they seem to appear in many of my programs where I might want to subset the list of variables to pick out just the numerics or just the character variables, because another variable list that is suppoprted in a data step  is a-numeric-b or a-character-B . put ( a-numeric-b )( =/) ; would just PUT the variables between A and B. ... View more

You could also use hash tables to collect all unique elements. In your sample data the position variable seems nicely sorted. Therefore Paige Millers' approach makes perfectly sense. If in reality that sequence is not guaranteed, you could initially add an additional variable with SequenceNumber = _N_; Then the selection as Paige is suggesting and finally sorting by SequenceNumber and dropping that variable again. ... View more

The way you are creating your input file will get you the wrong results.  You have to add an ampersand on your input statement. However, that said, try the following: data have; input var $ &; cards; 23,32 12.32 32 42 23ab $23 123 ; run; data want errors;   set have;   if notdigit(strip(var)) then output errors;   else output want; run; ... View more

Hi sunilreddy Based on your code I've done some testing. Linus is spot on: The majority of time spent is for maintaining the index. I was also under the impression that checking for the primary key constraint costed additional resources. The variables which make up your primary key and index are everything else than ideal as these are all chacter variables with quite a length. What happens is that your index file takes up almost as much storage space as your data file. Such an index is pretty useless. Is there any alternative key available which takes up less storage space? I understand you need to design and implement for an initial load and then for incremental delta loads. What volumes (how many rows) could these delta loads have and what's the load frequency (expected target table growth)? About duplicates: I believe you need to de-dup the source before loading (for efficiency reasons) and then to load with the indexes turned off. You need also to consider to set up a one off job for the initial load which then does a simple Proc Append. The indexes can then get created after the load (if needed at all). For the incremental loads: Not sure yet which approach to take. This depends on your DIS version and how much memory you have available (?). It also depends on the data volumes for incremental loads. Additionally: Will you inserts or also updates ("real" updates not duplicates or the same identical record sent more than once)? I've got DIS4.5 and played around with the SCD Type 1 loader. There are a few good things in it and the way to improve performance might be using a digest value together with an Xref table. Unfortunately the update code generated by this loader (in my un-patched version) appears to be very inefficient. So if worst comes to worst this might end up using the SCD Type 1 code as a starting point to set up a user written code node for loading. I'm attaching my DIS4.5 test job here. My be it's of some use for someone else. Can you please answer above questions and also give us any further insights you've gained since your last post? Thanks, Patrick P.S: I've added now also a .sas code version for testing which is based on your log posted so also people without DIS can eventually give you some hands-on support. ... View more

Assuming PRODUCT_LEVEL2_ID  is numeric : data have; informat month date9.; format month yymm6.; PRODUCT_LEVEL2_ID =123; REGION_ID = 234; CUSTOMER_ATTRIB_4_CD = 345; input Month REQ_F_P $ num1; datalines; 01MAY2011    2011Q2    1 01SEP2011    2011Q3    3 01OCT2012    2012Q4    4 ; data test; retain yMin yMax; set have end=last; yMin = min(yMin, year(month)); yMax = max(yMax, year(month)); output; if last then do;      PRODUCT_LEVEL2_ID = -999999999999;           do y = yMin to Ymax;                do m = 1 to 4;                 REQ_F_P = cats(y, "Q",m);                 output;                 end;            end;      end; drop yMin yMax y m; run; proc sort data=test; by PRODUCT_LEVEL2_ID  REGION_ID  CUSTOMER_ATTRIB_4_CD REQ_F_P; run; PROC TRANSPOSE Data=test out=testTR(where=(PRODUCT_LEVEL2_ID>-999999999999));      BY PRODUCT_LEVEL2_ID  REGION_ID  CUSTOMER_ATTRIB_4_CD  ;      id REQ_F_P;      var num1; run; PG ... View more

Hi:   I did not say to use left justify. I said to use the style override for LEFTMARGIN. There is a difference. See attached screenshot run using the Females in SASHELP.CLASS -- and then indenting only some of the names.. You could also try to use ASIS=ON style override if your data has embedded blanks. The problem with ASIS=ON is that, as a stored process, it is possible that your client application (such as PowerPoint or Web Report Studio might ignore any of the style overrides that you set up in PROC REPORT. I think it is more likely that ASIS=ON would be ignored than the LEFTMARGIN.   You might want to work with Tech Support on this because there might not be one solution that works for all the client apps that can receive your stored process. You may have to use more advanced macro programming to account for different indenting techniques for different client apps. And, there may be some client apps where you can't get indenting...for example...if you use the SAS Add-in for Microsoft Office and return CSV results to Excel, there is not anything you can do to preserve the indenting. cynthia   compute name;     if name in ( 'Barbara', 'Carol', 'Janet', 'Joyce') then        call define(_col_,'style','style={leftmargin=.25in}');   endcomp; ... View more

Hi:   As I illustrated in my code example, BREAK AFTER PGBRK/PAGE will NOT cause the title to change automatically on every page with a different slide number. If you had multiple PROC REPORTS, with a different TITLE statement for each PROC REPORT, then you could use your Macro variable for each title. Or, if you had a %DO loop, as I illustrated.   Unless you have multiple PROC REPORT steps with a different TITLE in each, OR you change your approach so that your macro program generates each PROC REPORT with its own TITLE (basically, you would be generating 1 PROC REPORT step for every page) -- the PROC REPORT that you show is not ever going to generate a different title using BREAK AFTER.   You say that you are "initiating slideno =1 for first page, slide no is incrementing for each proc report graphs. i.e. for each output object(table or graph) i am giving unique slide number in title." I see a macro program invocation that occurs 1 time (a call to %increaseSlideNo) -- but I only see that 1 time. You say you are doing this in a stored process. If you are executing your stored process multiple times and you want to get a different value for &SlideNo for every invocation, then you will have to use other techniques to make this happen. As I understand stored processes, every stored process invocation gets a fresh, new SAS session, with a fresh, new symbol table. So, unless you persist your session and save your former values for &SlideNo, every invocation of your stored process will probably start out with SlideNo=1, assuming you made the macro variable global and not local. I did not see a CALL SYMPUTX with 'G' option or writing to the global symbol table instead of the local table -- and with stored processes, you probably want to be sure you are putting &SlideNo in the global and not the local table.        Every new TITLE statement in your code replaces a previously issued TITLE statement. A single PROC REPORT step cannot change title statements for every internally generated page break unless you use BY group processing. Every time you invoke a stored process, as a new stored process, the TITLE statements that you use in your stored process code are like new. The title statements from the first invocation of the stored process are not around anymore when the second stored process starts up (unless you have done something to ensure that they are saved).   You might want to consider working with Tech Support on this. Considering the macro code you're working with, the fact that this is a stored process, and that you seem to want PROC REPORT to operate in a way that is not possible for it to operate, it's hard to put all the separate pieces together to make a coherent suggestion.  Working with a stored process and a macro program invocation in a stored process can change or should change your development approach (such as not having hard coded parameters and using a %GLOBAL statement and not having macro definitions nested within your %STPBEGIN/%STPEND invocations, understanding how you get a fresh symbol table for every stored process invocation, etc, etc. -- To me this needs someone to look at ALL your code and your data and help you figure out whether your current design  is working as you need, from a stored process perspective. cynthia ... View more

Hi:   PROC REPORT will not automatically break after every 4 report rows, unless you make a "fake" page break variable. Then, once you have your "fake" page break variable, you can either use a BREAK statement inside PROC REPORT or a BY statement. I don't know what you mean when you say that you need to "pass the column headings in next page break with previous group" -- because when page breaks come from either the BREAK statement or BY group processing, the column headings do get automatically generated for each page.   Consider the following program using your data and a fake page break variable. cynthia data test1; infile datalines; input first $ last $ num1; return; datalines; first1 last1  1 first1 last2  2 first1 last3  3 first2 last4  4 first2 last5  5 first2 last6  6 first2 last7  7 first2 last8  8 first2 last9  9 ; run;     proc sort data=test1; by first last num; run;    data final;   set test1;   order = _n_;   pgbrk=ceil(divide(order,4)); run;    ods listing close; ods pdf file='c:\temp\use_pgbrk1.pdf' notoc; proc report data=final nowd ;   title '1) Use PGBRK in BREAK AFTER';   column pgbrk first last num1;   define pgbrk / group noprint;   define first/group;   define last/display;   define num1/analysis;   break after pgbrk / page;   compute before _page_;     disp_line = 'Calc PageBrk Is: '||trim(put(pgbrk,2.0));     line disp_line $20.;   endcomp; run; ods pdf close;     proc sort  data=final; by pgbrk; run; options nobyline; ods pdf file='c:\temp\use_pgbrk2.pdf' notoc; proc report data=final nowd ;   by pgbrk;   title '2) Use PGBRK in BY PGBRK=#byval1';   column first last num1;   define first/group;   define last/display;   define num1/analysis; run; ods pdf close; title; options byline;     ods pdf file='c:\temp\use_pgbrk3.pdf' notoc startpage=no;  proc report data=final nowd ;   by pgbrk;   title '3) Use PGBRK with STARTPAGE=NO and automatic BYLINE';   column first last num1;   define first/group;   define last/display;   define num1/analysis; run; ods pdf close; title; ; ... View more

If you don't want single quotes, leave them out. As you have seen, the comma causes a problem because it marks the end of a macro parameter's value.  There are ways around it (some shown above by other posters) possibly this variation as well: %macro test (name1=%superq(var1)); It is not clear from your post why you would want to set up a default value for &name1.  But the syntax would be equally simple if you just used it to call the macro later without using a default value: %test (name1=%superq(var1)) Good luck. ... View more