Hi Thalo Try running the code that I posted above. At the end of the first step, you should have a dataset named "have" that has your data. It has two types of records; one with the id and account name, and one with the transaction details. They are still in the same order as on your report; so you have the ID and name for "Chew Dewey Barbeque", and then the two transactions, and then the ID and name for "Salmon River Plaza", and then the four transactions. What the second step does is: i) figure out is this an ID line or a transaction line; ii) if it's an ID line, keep (retain) the ID and name; iii) if it's a transaction line, output the record (which will have the retained ID and name). This will be dataset "want". Give it a try, and see how close it is to what you want. Tom ... View more

In that case, it's really easy. Just replace the "cards;" statement and the data with "infile "C:\directory\file.csv"; and you should be good to go. ... View more

Here's how I like to do these. The first step converts the data elements you need into a SAS dataset. I got one working based on the data you posted; you'll probably need to fiddle with it based on your full data. The idea is to keep ID and account name from the header rows, and the variables you need from the transaction rows. Check that the data in "have" look okay. Then in the second step, if the record is a header, just retain the ID and account name; they will automatically populate the transaction rows, which are the only ones output. Tom data have; /* Turn the report format into a SAS dataset */ length rowtype TransType $ 32 AccountName_in $ 64; drop rowtype; input; /* get a record, and hold it in internal variable _infile_ */ rowtype = _infile_ || repeat(" ", 32); /* Make sure we have at least 32 characters to work with */ if substr(rowtype, 1, 7) = "Loan #:" then   do;    ID_in = scan(_infile_, 2, '09'x, "O"); /* 09 is hexadecimal for a tab character */    AccountName_in = scan(_infile_, 4, '09'x, "O");    output;   end; else if substr(rowtype, 1, 6) = "Page #" |   substr(rowtype, 1, 4) = "Date" |   substr(rowtype, 1, 11) = "Loan Totals" then   ; /* we don't need anything from these lines */ else   do;    TransDate = scan(_infile_, 1, '09'x, "O");    TransType = scan(_infile_, 2, '09'x, "O");    TransAmount = input(scan(_infile_, 3, '09'x, "O"), best32.);    TransDisbursed = input(scan(_infile_, 4, '09'x, "O"), best32.);    TransCapitalized = input(scan(_infile_, 5, '09'x, "O"), best32.);    TransRecovery = input(scan(_infile_, 6, '09'x, "O"), best32.);    TransBalance = input(scan(_infile_, 7, '09'x, "O"), best32.);    output;   end; cards; Loan #: 04-0026 Account Name:  Chew Dewey Barbeque  Page #: 11      Date Action Amount Disbursed Capitalized Recovery Balance 07/09/2004 Commitment 1000 0 0 0 0 07/09/2004 Advance: Principal 1000 1000 0 0 1000 Loan Totals 1000 0 0 0  Loan #: 04-0037 Account Name:  Salmon River Plaza  07/12/2004 Commitment 27500 0 0 0 0 07/12/2004 Advance: Principal 27500 27500 0 0 27500 08/11/2004 Payment: Customized 600.8 0 0 0 27059.62 09/22/2004 Payment: Customized 545.8 0 0 0 26734.81 run; data want; set have; length AccountName $ 64; retain ID AccountName; drop ID_in AccountName_in; if ID_in ^= "" then   do; /* keep the id and account in our retained variables */    ID = ID_in;    AccountName = AccountName_in;   end; else output; /* this is a transaction row, and we've retained the ID and account */ run; ... View more

Hi, SASPhile I haven't done much with the Sybase engine, but I'm pretty sure both of the above suggestions will work with it. Here are a couple more: 1. In your first query, you seem to be doing a select *, but in your second one you're only asking for cm11 from your table. If you can change your first select to "select a.cm11", you may reduce your data volume enough to make the process acceptable. 2. As a different way to tackle Reeza's suggestion, check out the following note. It shows how SAS can set up a long "where ... in(...)" clause, as a substitute for the join. I've never tried this myself, but it looks reasonable. http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#n0tcetvx1zpnayn1r8actrkjrd5o.htm ... View more

Hi, Brooke I suspect the issue is the SAS server that you are using. When you use SAS with Display Manager (what you call Base SAS), the SAS code is running on your PC. When you use EG, you may be using a different machine (called a SAS server) to run your code. Because of that, the OLE connection information that is on your machine won't be available. First, confirm that this is the case. At the top of your program window, you should see a "Selected Server:" dropbox. If the selection is "Local", you are using your computer, and the problem is something else, which we'll try to figure out. But if the selection is anything else, your code is being submitted to a remote server, and you'll have to either use the OLE connection that exists on that server, or get the administrator of the server to set one up. I can assure you that what you are trying to do is possible. I've done it a bunch to a variety of databases. Tom ... View more

Hi, Shridharmanvi Yes, your example of completeness does fall into what I consider the "quality" perspective. Clearly, you need to ensure that (in your example) each process outputs 1000 records. But you might also consider analysing the quantity and type of errors being reported, especially if your ETL processes are transforming data in an automated manner. To give an example: I live in Ontario (Province) Canada, and my Postal Code is K2P 1S3. If a U.S. based system was processing my address data, it might transform my postal code to all numeric (ZIP code), and my ON province to OH. (Believe me, it's happened). Although "on paper" the system is functioning correctly, a large count of these transformations would indicate a systemic error, and should be investigated (because you're irritating all of your non-U.S. customers). In terms of the weights for different metrics, that's a business question for your organization (which must involve the participation of the warehouse users). However, I have seen cases where organizations make a priority of measuring the easy ones (technical "facts" like data volumes, memory, disk...), and ignore the important ones, like is the system meeting the needs of users (which can be VERY difficult to determine). I'm suggesting that the most important questions for you to answer are the ones that will get you fired if the executives don't like the answers! If the Assistant VP for Cost Compliance emails the VP Finance that "he can't find any of the stuff he needs in the warehouse, and why the Hell are we spending so much money on it", I would be VERY worried! Best,   Tom ... View more

I love Tom's solution (never seen that pattern before), but I'm also a big fan of PROC SUMMARY/MEANS. I was wondering if procedure startup code explained the time differences, so I ran some tests at higher volumes. Note that the times for "Tom" include a sort, as it is required for his pattern, whereas the SUMMARY doesn't need to be sorted. The times were: Summary Tables Howard Tom volume Real Time CPU Time Real Time CPU Time 25,000 observations 0:00.47 0:00.21 0:00.03 0:00.02 250,000 observations 0:00.38 0:00.51 0:00.16 0:00.28 2,500,000 observations 0:05.54 0:07.17 0:01.71 0:02.84 25,000,000 observations 2:46.00 2:06.00 1:23.00 0:41.00 Generated by the SAS System ('Local', X64_7PRO) on November 26, 2012 at 4:26:14 PM And as a chart: Howver, the 25 million row with SUMMARY run required disk work space, so I reran it with increased memsize. Here's the result: Note the vertical scale is log. So: Tom's technique is as efficient as it gets; PROC SUMMARY will somewhat catch up at higher volumes, but requires more memory; I just blew a whole afternoon, and enjoyed every bit of it! Tom ... View more

I would want to look at it from different perspectives: 1. Meeting requirements Are my users able to get the information they need out of the warehouse? Does the data in the warehouse need to be augmented to allow them to do their jobs better? Do they find any operations difficult, that could be remedied with documentation or utility development? When they have problems, is the resolution mechanism (help desk?) working efficiently? How effective is the warehouse for different classes of users: developers, power users, regular users, infrequent users? 2. Quality Are my ETL processes meeting required error rate thresholds? If the error profile is changing, is the underlying data changing in a way that required modifying the ETL? I'm not sure what you mean by completeness, but it probably falls here. 3. Performance What is the response time for a set of retrievals that exercise the different operations and data required? 4. Capacity How many people are using the warehouse? How many concurrent active users are using the warehouse? Can the warehouse handle projected growth at the required performance? 5. Throughput Are my daily load/update processes running in the required window? At projected growth rates, how soon will I exceed my window? Do I have any workarounds (parallel processing, etc.)? 6. Size How big is my warehouse? Do I have enough disk and memory for the current size? At projected growth rates, how soon will I need to upgrade disk and/or memory? Am I approaching any limits in either the software or hardware, that can't be expanded? Tom ... View more

And in SQL: PROC SQL;    CREATE TABLE WANT AS    SELECT Period,             (MAX(ColA)) AS ColA,             (MAX(ColB)) AS ColB,             (MAX(ColC)) AS ColC       FROM HAVE       GROUP BY Period; QUIT; Tom ... View more

Are your Enterprise Guide users using the SAS installation on their local machines? Or are you all using a common SAS server environment? What version of Enterprise Guide and SAS are you using? Tom ... View more

Anybody at SAS listening? There are "Express" versions available for pretty much all of the DBMS products, and for development environments like Visual Studio, that let people with small-scale requirements familiarize themselves with the products. Neither SAS nor SPSS has one of these; I suspect that the first to introduce one may gain significant market share among people like the users of this forum. [Tom steps off soap box.] Tom ... View more

1. For the first two groups, the group will be different because it's based on the head of the tree, not the tail, but the information is identical, and could be switched with a SQL transformation. 2. For the last two groups, I was only working with the OP's original post, and hadn't made it far enough to see the discussion about identical parts with different IDs. As far as I can see, PROC BOM doesn't support compound keys, so I did a quick SQL transform before and after to blend ID and Previous/Current. I believe the results are now equivalent. Tom proc sql; create table _have as   select ID, Current, Previous,    ((ID * 1000) + Current) as _Current,    ((ID * 1000) + Previous) as _Previous   from work.have; quit; proc bom data=work._have out=work._want(keep=ID Current Previous _Part_ _Parent_ _Prod_); structure / parent=_Current component=_Previous ID=(ID Current Previous); run; proc sql; create table want as   select ID, Current, Previous,    _Prod_ - (int(_Prod_ / 1000)*1000) as Group   from work._want; quit; ... View more