Where clause in datastep or sql? data want;      set have;      where past_due=2; run; ... View more

Pretty straightforward in Base SAS then: data have; attrib A format=$1. X B C format=best.; infile datalines; input a $ x b c; datalines; a     1      2     1 b     1      1     1 c     1     -2     0 a     2      3     2 c     2      4     1 a     3      0     0 ; run; proc sort data=have;   by a x; run; data inter;   set have;   by a;   retain result;   if first.a and b > 0 then result=1;   else do;     if b > 0 then result=result+1;     else result=0;   end; run; As for doing it in a query then that is a bit more tricky as there is no retain options. ... View more

This excellent blog gives you examples on pretty much everything 9.3/9.4 SGPLOT/GTL. http://blogs.sas.com/content/graphicallyspeaking/ ... View more

Hi, There isn't enough information there to do it right at the moment.  You need to identify a sort order first, i.e. what makes a, B=2 appear prior to the record a, b=3.   ... View more

I would concur with all the points above.  It depends greatly on scale of project, number of people working on the project, and how things operate within the project.  There are positives and negatives to each scenario.  One thing to bear in mind is that nowadays there is a big shift towards programs which are easy to read and maintain rather than compressed, complicated code due the obvious reasons that storage is no longer such an issue and speed of execution for most people also not too much of a problem.  However coming to a project 6 months after all the developers have left and then trying to work out what that funky bit of code does can be resource intensive, and can also be quite complex to validate. ... View more

Perhaps you could post an example file which causes you this problem? ... View more

It shouldn't matter, if the year is character than it will appear as 2014_xxx, if you put it as numeric SAS will add an underscore _2014_xx as first character has to be not number.  The error above is telling you the what you are grouping by is not distinct.  You need to examine your full table, identify which are the groups to transpose by.  I have added code to the inter datastep to assign a unique number to the groups I could see, however you will need to look at your data and change for your purposes.  So my table prior to transposing looks like this: IDLAB               YIELD               BYGROUP 2004_Corn          33                    1 2004_Soy            32.5                 1 2005_Corn          33                     1 2005_Soy            32.5                 1 2006_Corn          16.9                   1 2004_Corn          9.04                    2 2004_Soy            16.8                   2 ... As you can see the 2004_Corn appears only once in Group 1, and Once in Group 2.  If it appears more than once then you get the above error.  So identify your groupings and make sure they are distinct. ... View more

I am afraid I don't know of a direct way of doing that.  The specific missing character functionality you are talking about is a SAS specific item, Excel does not have that as missing is just missing.  So from before, you would need to convert it character for Excel to show characters.  The only other options I can think of is: - Change your actual missings to a specific number which can't happen, e.g. -999999. - Post process the XML file which you generated, so read it in as a text file and search for: <Cell ss:StyleID="data__l1" ss:Index="1"><Data ss:Type="Number"> xx </Data></Cell> Then write your specific character in where the xx is. - Alternatively use Excel to replace blanks after export. ... View more

Hi, Sorry, not sure what your example is trying to do.  I have changed your code slightly to get it working, however the output from the tabulate looks pretty much identical to the input dataset other then three new variables _TYPE_, _PAGE_, _TABLE.  Also I note that the proc tabulate itself is not honoring the missing value per the dataset as you have no indicator - only for the datastep.  So try using "options missing=B" at the start of your program. This code works and shows 'B' in the output, it does this by putting the value into a character variable and using that.  (Note you can use the out= from the proc tabulate, and then post process that before outputting to Excel). options missing=B; data test;   missing B;   input v_id $ v_value; datalines; 1 1 2 0 3 .B 4 0 5 -1 6 55 8 4003 ; run; data inter;   set test;     attrib my_val format=$10.;     my_val=strip(put(v_value,best.)); run; ods _all_ close; ods tagsets.excelxp file="S:\Temp\Rob\test.xls" style=statistical options (frozen_headers="1" absolute_column_width="none" zoom="80"); ods tagsets.excelxp options (sheet_name="Test"); proc report data=work.inter nowd split="~"             style(header)=[background = #DBE5F1 foreground = #000099 font_weight = bold ]             style(column)=[just=l borderrightcolor=white borderleftcolor=white                            borderleftwidth=1 bordertopcolor=white borderbottomcolor=white                            borderrightwidth=1 bordertopwidth=1 borderbottomwidth=1];   columns v_id my_val;   define v_id / "Id";   define my_val / "Value"; run; ods tagsets.excelxp close; ... View more