1. I ran the earlier code you posted, with some made-up values for the input data, and didn't have any problem. I don't see any heavy use of resources that should cause any problems; it's just a very long process because it does a lot. 2. I agree completetly with Art; this job is producing a huge amount of output. I find it hard to imagine that you'll be able to do anything useful with it. 3. Exactly what diagnostic are you receiving? Is it possible to post a piece of the log that contains the message? Do you have any indication of which step is failing, and how many records had been processed when it failed? 4. If not, try to find out where the problem is. As Art was able to run the first step, I assume it's the second step that's the problem. If you insert the following line immediately after your set statement, the log should contain a line for every thousand records read. if mod(_n_, 1000) = 0 then put _n_ =; Tom ... View more

Hi, mrainer Looking at your request, I believe your question pertains completely to the SQL syntax for DB2. Based on this, I suggest that you pursue this with IBM and Oracle. While SAS users connect to DB2 and Oracle, we aren't necessarily experts on the implications of the SQL dialects that they provide. I think that your best course of action is to get your query working in native Oracle, and then using it with SAS should be very straightforward. Tom ... View more

Check out SAS missing values. They represent a concept of "this isn't a number". Change Tom's format to: proc format ; value nadollar (default=12)  .='N/A' other=[dollar12.] ; run; And your code becomes: if xa = '4.1' then ya = .; if xa = '3.1' then za = .; ... View more

Fair enough. I still vote for the fuzz=0, but fortunately both will work fine in the vast majority of cases. Weather here in Florida is Sunny 80's...how's the weather in the Great White North? Tom ... View more

No, I don't think there's any difference. From the attached code, it appears that numbers between around 4.7 and 18.8 can't accurately represent an "x.1" in floating point, therefore the 5.9, 6.0, and 6.1 are problems, while the 3.4, 3.5, 3.6 and 19.9, 20.0, and 20.1 are fine. Yes, the problem is that the .1 loop increment causes problems, but it's compounded by the uncertain results of using a non-zero fuzz. Here's your example, Art, with some additional formats: PROC FORMAT;   VALUE fmta (fuzz=0)        . = 'Missing'        Low -3.499999999 = '0 - not including 3.5'        3.5 -5.999999999 = '3.5 - not including 6'        6.0 -19.99999999 = '6.0 - not including 20'        20.0- HIGH =  '20 and above';   VALUE fmtb        . = 'Missing'        Low -3.499999999 = '0 - not including 3.5'        3.5 -5.999999999 = '3.5 - not including 6'        6.0 -19.99999999 = '6.0 - not including 20'        20.0- HIGH =  '20 and above';   VALUE fmtc (fuzz=0.0000000001)        . = 'Missing'        Low -3.499999999 = '0 - not including 3.5'        3.5 -5.999999999 = '3.5 - not including 6'        6.0 -19.99999999 = '6.0 - not including 20'        20.0- HIGH =  '20 and above';   RUN; DATA ONE;   do i = 0 to 21 by 0.1;   j= i;   k= i;   l= i;   format i best19.15;   format j fmta.;   format k fmtb.;   format l fmtc.;   OUTPUT;   end;   RUN; Note that the "nominal" 6.0 is a problem with the fuzz=0, because it is actually less than the integer 6. Now in another example using the "less than but not equal to" format syntax: PROC FORMAT;   VALUE fmta (fuzz=0)        . = 'Missing'        Low -< 3.5 = '0 - not including 3.5'        3.5 -< 6 = '3.5 - not including 6'        6 -< 20 = '6.0 - not including 20'        20 - HIGH =  '20 and above';   VALUE fmtb        . = 'Missing'        Low -< 3.5 = '0 - not including 3.5'        3.5 -< 6 = '3.5 - not including 6'        6 -< 20 = '6.0 - not including 20'        20 - HIGH =  '20 and above';   VALUE fmtc (fuzz=0.0000000001)        . = 'Missing'        Low -< 3.5 = '0 - not including 3.5'        3.5 -< 6 = '3.5 - not including 6'        6 -< 20 = '6.0 - not including 20'        20 - HIGH =  '20 and above';   RUN; DATA ONE;   do i = 0 to 21 by 0.1;   j= i;   k= i;   l= i;   format i best19.15;   format j fmta.;   format k fmtb.;   format l fmtc.;   OUTPUT;   end;   RUN; The 20 results are problematic with the fuzzed formats. It's my contention that this format syntax combined with fuzz=0 is the only combination that accurately represents all of the numbers. But I'm sure this isn't over! Tom ... View more

This is quite straightforward in Enterprise Guide: 1. If your dataset has more records than you're interested in, get rid of the unwanted ones with a Query Builder step. It's not clear from your description, but if you want ONLY 2010 and 2011 data, and have other years in the dataset, extract out only 2010 and 2011 into a temporary dataset. 2. Create a Query Builder step, and add item_number and price into the "Select Data" tab. (This assumes that item_number is the only thing you care about for duplicates. If you need to use location and product, add them as well. 3. Add price a second time (the resulting column name will probably be price1. Change that to count). In the Summary box, select "COUNT". That should turn on summary groups, and automatically add all of the other columns to the grouping. 4. Go to the "Filter Data" tab. In the "Filter the summarized data" section, set a filter to count > 1. 5. Run the query. What is should do is group the data by item_number and price (and location and product if you added them), and calculate the count of records. Then the "having" clause will select out the result records with a count > 1. This dataset identifies all of the itemno / price combinations that you want, but it's only one record per result. (Note that there are a number of other ways to do this, but this should get you started). Now you need to extract all of your original records that match. This is really easy. 1. Create a Query Builder step on the result dataset you just created. 2. Click the "Add Tables" button, and add your original dataset. Click the "Join Tables" button, and make sure that your two tables are joined with inner joins on itemno and price. 3. Move all of the columns from your original dataset to the "Select Data" tab, and run the query. This should be the results you want. Tom ... View more

Just to finish this one off, I corresponded with SAS Tech Support. It turns out that as the example above shows, if there is possible overlap in the ranges due to the FUZZ value, the results can be unpredictable. In cases of continuous ranges, FUZZ=0 is the best choice. Tom ... View more

Your response talks about outputting a few tests and controls, but your code will output one record for every input record. So first of all, you should have the same number of output records as input records; that's what you're asking for. Taking group 3 as an example: any group 3 records in the first 17,500 records will be output with SEL_Nope='2137' and Outfile=3 the group 3 records between record 17,501 and 35,000 will be output with SEL_Nope='2145' and Outfile=5 same idea for group 3 records between record 35,001 and 52,500 Starting at any group 3 records on or after the 52,501st, your next set of if / then/ else statements will be processed, which will use the values of Stream, buyer, and Age to set SEL_Nope and Outfile. The records will then be output. And finally, as these statements don't look exhaustive, any records that don't match any of the if criteria will have SEL_Nope and Outfile set to missing (.), and output. Was this what you were trying to do? Tom ... View more

Why do you think it's not working correctly? At a quick glance, it looks fine to me. Tom ... View more

You posted this in the Enterprise Guide forum, so I assume you want to do this with Enterprise Guide. 1. Start up enterprise guide, and select a "New Project". 2. In the Server List, find your dataset and double click on it to open it into your project. 3. You should now be able to select "Describe" and then "Summary Tables Wizard". 4. Follow the wizard. Select the variable that you want the percentages for (maybe Revenue or Sales?)  as the "Analysis variable". The Statistic will be "Sum" by default; change it to % of column sum. Use the variable that classifies your businesses into small, medium, and multi as your classification variable; put it into the rows. Click "Finish", and see if you like the results. Tom P.S. Enterprise Guide has a tutorial, which you will probably find very useful. ... View more