Solution using hash tables power to ease things up... You've gone the right way using ordering, however, you misunderstood how your datarows are appended to the hash object when you don't use a dataset: statement. You would need to use a hash iterator object to retrieve first or last 3 hash object items after all of it has been loaded and sorted. Here is a simple hash solution that still uses data step to output. You could use hash object output but you would need to create 2 or 3 hashes for simplicity or do some hardcore .remove() implementation. Data have;     Input Name $ Score; Datalines; A.B. 6 C.D. 10 E.F. 5 G.H. 3 I.J. 10 K.L. 9 M.N 3 ; Run; data want1 want2;     if 0 then set have; /* avoid length statements */     If _n_=1 Then Do;         Declare Hash ht(dataset: 'have', Ordered:'d', multidata: 'Y'); /* load*/         Declare Hiter hi('ht'); /* hash iterator object declared on hash object HT */         ht.DefineKey('Score');         ht.DefineData('Name','Score');         ht.DefineDone();     End;      /* to achieve want1 and want2 logic */      i=0; j=0;      do while (j<3);          hi.next(); /* loop through sorted data using iterator. If iterator has not been used before, then hi.next() is equivalent to hi.first() */           if i<3 then output want1;           output want2;           i+1;           if last_score NE score then j+1;           last_score=score;               end;      drop i j last_score; run; I've put the interesting hash elements in bold the rest is similar logic that you would hard code if you used sorted data. Please note the use of multidata:'y' option as otherwise only CD or IJ could exist within the hash object as their KEY is identical. ... View more

If the encounter repeats in both data sources, there will still be a duplicate entry in your merged data. data one;      input encounter fakedata;      datalines; 1 1 1 1 1 2 ; run; data two;      input encounter fakedata2;      datalines; 1 3 1 4 1 5 1 6 ; run; data tester;      merge one two;      by encounter; run; Data step merge does NOT support many to many appropriately and still will manage one-to-many without failing output. If you notice the TESTER result, this is what happens: starting with the first record for a by variable in each dataset, merges 1:1 until either data set runs out of records for the by group at which points the last value is retained and output with all subsequent records from the other source that have the same by value. Hence this does by no mean kill duplicates from either set. So in short, no it does not solve your issue. At least not how you've worded it. ... View more

Hi DN, I could not find any documentation about TICKDISPLAYLIST in SAS9.2 help and an online google search over :SAS gets a forum post dating from jan 2012 that you had initiated about this particular option. However, when I try to run the code, SAS gets a critical error and shuts down. I suspect it is because it is an option that is only applicable within LINEAROPTS= and not DISCRETEOPTS= but that's merely my guess. Or maybe it only is for the X-axis which is why I had asked if any other graph type could achieve similar box results naturally with numeric instead of category data. In particular, I also found this with TYPE= documentation for axis/overlay DISCRETE Use a DISCRETE axis if possible. The data for discrete axes can be character or numeric. For an X or X2 axis, you can add a DISCRETEOPTS=( ) option list to customize this axis type. DISCRETEOPTS= is not supported on a Y or Y2 axis. Nevermind that, It was an issue with ods settings since Friday. It does indeed solve the issue DN. I am actually surprised to see it is supported despite the cute warning in the documentation that I did not strikethrough though. The only issue with this approach is that it is less generic than a format. That is, there would need to be a recompiled proc template each time the right-axis values change whereas a format-based approach does not. Sadly discrete axis seem to have very little support for layout overlay, let alone documented support! ---------- Sami, The issue is that groups are formed according to the formated variables. My code example solved the problem if only the left axis had duplicate values because it applied formatting after axis creation. I can't seem to find any tool for discrete Y-axis that can support options like tickvalueformat or the like. There aren't any format related tools like NOTSORTED + preloadfmt for, say, proc tabulate that could help you create 2 distinct grouping when they have identical formats. So really, the issue boils down to having formatted values overlap on a discrete Y-axis. As per Sanjay's reply to my first post, if you could use the NEEDLE chart instead of BARCHART to create the horizontal bars exactly how you want it, then you could specify that even the Y2 axis is of TYPE=LINEAR, you could use the same LINEAROPTS= for TICKVALUEFORMAT and achieve the desired results as then the formats would only replace the values after axis creation. With this approach, you could have a generic template transferable accross different data (diff number of ticks, tick spacing etc and could forward the template with some documentation on how to set the data file to collegues for example). If DN solution fulfills your requirements though, then there is no justification to learn how to mimic barchart using needles plot. Glad to see your issue was resolved. Vince ... View more

TICKVALUELIST and TICKVALUEFORMAT can be worked to get the left axis values formatted appropriately (you remove the paramn trtsdf. from the format statement and use that as value for tickvalueformat). The issue is that if you do this, the scatterplot no longer alligns with the barchart as the barchart MUST have a category variable as X axis and thus there's no concept of distance between .8 and 1.2 Since the first graph you've defined needs category variables, the left axis gets defined as a discrete axis and because of formats pointing to the same values, you create 8 charts. The most simple fix is to change your format trtsdf to make all of the labels distinct. You might be able to play with truncation of labels on the graph to create a long format like 0.2="pos                       1" and truncate the end to mimic your desired output. Otherwise, you can change the data and the tick values so that you can line up a numeric left Y axis perfectly with a categorical (spread evenly) right Y axis. This can be seen by the code example below : Alternatively, I assume you could've added a data point of missing values for 1 and formated it as "" to create a space inside your graph retaining the .2/.4/.6/.8 patterns. I put to bold the changes I made. proc sql;    create table uni_api    (paramn num(8), paramn1 num(8), estimate num(8),standard num(8));    insert into uni_api    values (0.25, 0.25, -3.0, 0.3)    values (0.5, 0.5, -2.8, 0.4)    values (0.75, 0.75, -3.1, 0.2)    values (1, 1, -3.4, 0.4)    values (1.25, 1.25, -2.7, 0.6)    values (1.5, 1.5, -2.9, 0.3)    values (1.75, 1.75, -3.3, 0.2)    values (2.0, 2.0, -3.1, 0.1); quit;   PROC FORMAT;     VALUE trtsdf     0.25="pos"     0.5="unc"     0.75="neg"     1.0="x 200 mg"     1.25="pos"     1.5="unc"     1.75="neg"     2.0="x 400 mg"     ;     VALUE trtpf    0.25=" 12 (4.67%)"     0.5=" 11 (8.03%)"     0.75=" 13 (23.31%)"     1.0=" 61 (  100%)"     1.25="  8 (5.69%)"     1.5=" 10 (26.61%)"     1.75="  8 (76.69%)"     2.0=" 51 (  100%)" ;   QUIT;    PROC template;     DEFINE STATGRAPH gtplot;     BEGINGRAPH;     LAYOUT OVERLAY / XAXISOPTS=(label=" "                      linearopts=(tickvaluesequence=(start=-5 end=0 increment=1)))                      YAXISOPTS=(type=linear label="Treatment / Dose" linearopts=(tickvaluelist=(0.25 0.5 0.75 1.0 1.25 1.5 1.75 2.0) tickvalueformat=trtsdf.) )                      Y2AXISOPTS=(label="N(%)");      BARCHART y=estimate x=paramn1 / GROUP=paramn BARWIDTH=0.4  NAME="bar" ORIENT=horizontal           FILLATTRS=(COLOR = LIGHTGREY) DATATRANSPARENCY=0.7 YAXIS=y2;      SCATTERPLOT x=estimate  y=paramn  / GROUP=paramn XERRORLOWER=eval(estimate-standard)      ERRORBARATTRS=(thickness=1)      LEGENDLABEL="Results" NAME="SCATTER"      MARKERATTRS=(color=black size=0);      BARCHART y=estimate x=paramn1 / GROUP=paramn BARWIDTH=0.4 PRIMARY=no NAME="bar1"                                      ORIENT=horizontal YAXIS=y2                                      FILLATTRS=(COLOR = LIGHTGREY) DATATRANSPARENCY=0.7 ;      ENDLAYOUT;     ENDGRAPH;     END;   RUN;   PROC SGRENDER DATA=uni_api TEMPLATE="gtplot";     format /* paramn trtsdf. */ paramn1 trtpf.;   RUN; Hope this was useful, I've learned a lot myself trying to solve this puzzle -_-. Sadly, I lost the help page that clearly states that if you attempt to overlay graphs for which the axis' are not compatible, you can get unexpected results. If there exists a bar-making plot that can use numeric points and a width instead of categorical data, you could then get more generic in terms of how you set our paramn value ticks but I couldn't find any in the documentation.(maybe its achiveable with histogramparm with very painful handling of negative values). Vince ... View more

I don't have 9.3 on my current machine to test but from the description and my understanding of data step help wording conventions, I would assume it mimics the &SQLOBS with some limitations so if there is no SET statement in a data step, the value returned should be 0 as no the only dataset in an empty data step is that of the output but no observations are READ from the output datasets. Similarly, I think proc datasets use VTABLES which are really only views and thus technically don't directly open a data set to read in the values. I'm not entirely sure why it doesn't output -1 though I'd have to get on a 9.4 test tower at work to run tests and someone else will likely provide a detailed answer before then but for data step functionalities, I suggest you test the following data _1;      x=1; run; %put &sysnobs; /* should be 0 if my understanding is correct */ data _2;      x=1; output; x=2; output; run; %put &sysnobs; /* should be 0 as well */ data _1_2;      set _1 _2; run; %put &sysnobs; /* should be either 2 or 3 depending if it considers the entire set statement entity as a single "opened dataset" */ /* create views for each _1 and _2 call them v_1 and v_2 for simplicity */ data _null_;      set _1; run; %put &sysnobs; /* should be 1 */ data _null_;      set v_1; run; %put &sysnobs; /* will likely be 0 that would explain the proc datasets results - if not, repeat with v_2 */ data _null_;      set _2(where=(x=1)); run; %put &sysnobs; /* should be 1 as technically where statements within the set statement are more efficient than where statements within the data step as it skips reading the record entirely */ With the above, it should give you insight on what to expect from various "usual" procedures but odds are you'd need to do tests on a per procedure basis to really understand how it works. In general, I would expect it to return the number of physical observations attached to the dataset tied to the DATA= statement for procedures that have that in their statement options. ultimately, I suppose it's main use or purpose is to get the number of physical observations from sets located on a rdbms where you can't use nobs or nlobs from the sashelp.vtable to get the information. ... View more

Well again, thanks DN for the valuable lesson. Thank you Hai Kuo for the links *edit SAS9.2 documentation did not have any TIP with regards to %nrstr :smileyshocked: ... View more

Hi DN, As per call execute documentation Details If argument resolves to a macro invocation, the macro executes immediately and DATA step execution pauses while the macro executes. If argument resolves to a SAS statement or if execution of the macro generates SAS statements, the statement(s) execute after the end of the DATA step that contains the CALL EXECUTE routine. CALL EXECUTE is fully documented in SAS Macro Language: Reference. My understanding (I had to do some research when you corrected me a few days back) was that the macro with sas code will be compiled (even without %NRSTR) but will actually have a delayed execution as if you had done successive macro calls immediately after the run statement because sas code was found in the macro. As with the following example: 394  data h1; 395      x=23; 396  run; NOTE: The data set WORK.H1 has 1 observations and 1 variables. NOTE: DATA statement used (Total process time):       real time           0.31 seconds       cpu time            0.00 seconds 397 398  data h2; 399      x=11; 400  run; NOTE: The data set WORK.H2 has 1 observations and 1 variables. NOTE: DATA statement used (Total process time):       real time           0.00 seconds       cpu time            0.03 seconds 401 402  data have; 403      id=1; output; 404      id=2; output; 405  run; NOTE: The data set WORK.HAVE has 2 observations and 1 variables. NOTE: DATA statement used (Total process time):       real time           0.02 seconds       cpu time            0.01 seconds 406 407  %macro ex(id=); 408 409  data _null_; 410      set h&id.; 411      put x=; 412  run; 413  %mend; 414 415  data _null_; 416      set have; 417      call execute('%ex(id='||id||')'); 418  run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).       417:29 SYMBOLGEN:  Macro variable ID resolves to 1 SYMBOLGEN:  Macro variable ID resolves to 2 NOTE: There were 2 observations read from the data set WORK.HAVE. NOTE: DATA statement used (Total process time):       real time           0.32 seconds       cpu time            0.00 seconds NOTE: CALL EXECUTE generated line. 1   + data _null_;     set h1;     put x=; run; x=23 NOTE: There were 1 observations read from the data set WORK.H1. NOTE: DATA statement used (Total process time):       real time           0.01 seconds       cpu time            0.01 seconds 2   + data _null_;     set h2;     put x=; run; x=11 NOTE: There were 1 observations read from the data set WORK.H2. NOTE: DATA statement used (Total process time):       real time           0.00 seconds       cpu time            0.00 seconds So I believe it is only necessary to wrap with %nrstr if the variables coming from the data step contain either & or % etc which would resolve inappropriately for whatever reason. Hopefully I didn't miss your point ... View more

So you want your existing macro executed on every single customer found in a dataset? %macro yourmacro(client=)      /*already coded macro*/ %yourmacro; data _null_;      set have;      call execute('%yourmacro(client='||clientvarname||')'); run; The same can be done with an infile statement. This will effectively execute the macro that you've defined before as %yourmacro(client=ABC); If you needed the client named within quotes for your macro, you can wrap clientvarname in quotes. There are probably a handful of other ways and some might be preferable to your desired logic. If your macro creates macro variables through a data step or proc, those macro variables won't be available until after the data _null_; is executed as call execute only executes macro functions immediately and rather create a stack of macros to execute when %yourmacro is not a macro function. ... View more

As far as I am aware, or at least this is how I've done it for my projects, the only alternative when you dislike the output layout of proc freq LIST for nway contingency tables is to use proc tabulate. I will try to find a link to it. I had posted a similar question and Ballardw had provided a very helpful response as to how to write proc tabulate to generate the nway effect. Otherwise, I simply recommend you start by reading the sas documentation about it and come back with additionnal questions on how to code the proc tabulate to achieve your output. Vincent ... View more