I am not a user of Teradata but a quick search of SAS support confirms that the default behaviour of Teradata for updates is one row at a time. Suggest you try the DBCOMMIT = option to get Teradata to commit your updates say every 1,000 or 10,000 rows. Also suggest you investigate the Teradata MULTILOAD capability, This will not only improve update performance it will probably reduce query log entries. Check also with your DBA to see if there is a Teradata option for switching off the log messages. ... View more

Does your data also record a start date and finish date? If so then you can combine the dates and times to get what you want like so: start date time = dhms(start date,start hh,start mm, start ss); end date time = dhms(end date,end hh,e nd mm, end ss); duration = end date time - start date time; format duration datetime. ; If you don't have start and finish dates then its a bit harder. Can a procedure last more than one day? If yes then you have a problem! If not then you can assume if end is less than start then it has gone over just one day boundary: if end time < start time then duration = end time + 24 - start time; else duration = end time - start time; ... View more

My suggestion is that traffic be monitored on this slow link to determine when and how often the line capacity is "maxing out". Perhaps this has happened already. If not then this would help establish a baseline level of performance to correlate with when response times become unacceptable. It may even be possible to run some simple tests with just one or two EG users and monitoring the line at the same time. Really I don't think there are any magic numbers you could use as network usage is so site-specific. Also what about non-SAS traffic on this line? At least line monitoring should give you some real numbers to justify the expense of doing an upgrade. ... View more

You could try something like this: data new; set old; retain last_v1; if last_v1 ne v1 then v2 +1; last_v1 = v1; run; On the first row last_v1 will not be assigned so v2 is set to 1. On row 4, where there is a change v1 will not equal last_v1 so 1 is added to v2. ... View more

I suggest you investigate the MEANS procedure. The combination of the MEAN statistic with the WEIGHT statement will do a weighted average. If I understand your description correctly it appears you are weighting by the number of accounts. Your skeleton code will probably look like something like this: proc means data = xxx nway; where risk type in R1 to R5; class relationship; var dollarprofit; weight Number of accts; output out = xxx_out mean = ; run; You can also do weighted averages in the REPORT procedure but the learning curve is much higher. ... View more

Firstly with two WHERE statements, the last one will supercede the previous one so that one is the only active one. If you want both to work, which your conditions confirm, then you need to change your second WHERE to WHERE ALSO. Alternatively you can combine the two into one like so: where deprived =1 and PEFAMTYP_M=7 and (MD_Q02=1 or MD_Q06=1 or MD_Q14=1); ... View more

Suggest you search the SAS Support site for EG 4.22. You will find a list of software fixes for this release (essentially a maintenance release). You should then be able to decide if upgrading is worthwhile or not. ... View more

The simple answer is no! You are trying to run SQL inside a DATA step. SAS steps, either DATA steps or procedures (including the SQL procedure) run sequentially, one after the other, never one inside the other. You can do your updates all within a DATA step but you need to explain in more detail what you are trying to do. ... View more

If you are simply wanting a quick to produce, generic test of PC performance I would be tempted to use the test programs but repeat them in a loop so that they take enough time to be able to do comparisons between PCs. If you looped these programs so that they took say 30 minutes, then you would start to see significant variations between PCs. ... View more

If you don't use SAS/CONNECT there are 2 options: 1) Copy the source code that produced the format and run it on the target computer. 2) Convert the SAS format into a SAS transport file using the CPORT procedure, do a BINARY transfer of the file to your target computer, then read in the transport file using the CIMPORT procedure. Refer to SAS documentation for more details. ... View more

I suggest you have a look at the test programs SAS provides to test that a SAS install has worked correctly. There is a different program for each product so the nice thing about these is the broad range of tasks tested so they should test all aspects of your PCs performance. A search through the SAS install directory for SAS program names including the word "TEST" should locate these. ... View more

In addition to Linus's comment, I suggest you search the SAS Support site using the keywords DB2 and bulkload. There are lots of resources that will help you. There are apparently 3 supported methods for bulk loading with the CLI LOAD method being recommended for very large loads. You will find examples of how each of these are used. ... View more

As Chris has suggested, it is possibly a slow, constrained network that causes this. Do you consistently get around the same time or is it faster outside usual work hours? I suggest you convert the spreadsheet to a comma or tab delimited file (FILE SAVE AS in Excel) and try reading it in as a delimited file not a spreadsheet. Then compare the elapsed time. ... View more

There is also the question as to how you transferred your mainframe file to Unix. You should only use EBCDIC informats if the file was transferred in BINARY mode so that the file is still EBCDIC-encoded. If the file was transferred as TEXT then the translation to ASCII would have happened automatically so that you don't require the EBCIDC informats. If your input file just looks like standard text in a Unix text editor its already in ASCII. ... View more

One possibility is to summarise and order your data first using the MEANS/SUMMARY and SORT procedures then define your ORDER and GROUP variables as DISPLAY instead. ... View more