Hi   I have a table where each row contains columns that have 'companies' and 'share owners' and the number of shares owned.  Any owner can have multiple rows of companies that they have shares in, and many owners may have shares in many companies.  Also companies can have shares in other companies as owners. This is a very large table with over 10 million rows.  The Owner_id (say 1002) in the first row is the same entity as the Company_id in the second row, so therefore owner_id 1002 is linked to company_id  1001, which is linked to company 1003) and company_id 1002 is linked to owner_id 1011, which also has shares in company_id 1004, and so on.   The 'have' data looks as follows: Company_id $4. Owner_id $4. Shares 8. Company_id Owner_id Shares 1001 1002 100 1002 1011 400 1003 1001 2500 1004 1011 1000 1005 1012 800 1006 1007 120 1007 1013 450 1008 1015 250 1009 1014 150 1010 1015 4500 1020 1001 25000 1021 1020 100000   What I would like to create  is a table for input to another process that has, for each owner_id,  each company they have shares in (as above) but also each company that they have shares in that has shares in other companies (secondary owners).  I'm aware that with such a larger table the output could be in the billions of rows, so I was thinking to limit the number of rows by filtering the number of shares for secondary owners say to 1000 shares to allow, for example, linking ACME (1002)  having 100 shares in Ford (1001), but not including Ford (1001) having 25,000 shares in McDonalds (1020), which may have 100,000 shares in Microsoft (1021) etc).    So the output for ACME might be: Company_id Owner_id Shares 1001 1002 100 1002 1011 400   And so on.   At present I'm testing assumptions on a table with 10,000 rows, but I am wondering if something like proc cluster, proc tree or proc distance of any similar procs might give me what I want (even if I have to eliminate the large corporate holdings myself).   Hope this is clear enough for assistance,    Thanks for any advice. ... View more

I have a large dataset (just over 15 million rows) that contains details of entities and their reported owners.  I've loaded the data into Viya VA and I'm able to produce a network diagram from that, but the client wants the network diagram output to a list table. From the table I've been given there are multiple cross entity/owner relationships where an entity can be an onwer and an owner an enity - the client wants all identified relationships.  The data has elements such as  entity 'a' is owned by 'b', entity 'c' is owned by 'b', and entity 'd' is owned by 'c'.  An example of the output required is a two column table (without the explanation in brackets): entity | owner a | b                           (a is owned by b) c | b                           (c is owned by b) d |c                            (d is onwed by c) d |b                           (d is owned by b which also owns c)   etc. I'm not sure if I should try to create a SAS table that I can use in the list table in SAS first and load that table into Viya VA, or to continue to try to wirite filters and calculated data items and add other objects in VA to massage the 15 million row data into the form required for the list table (which will be filtered by 'owner'), or to try to find a way to use the VA network analysis object and to write the result out to a list table.  The network analysis object in VA handles the data well when it's filtered and produces a diagram that looks correct, but as  the client wants the details in a table, I'm struggling to make this all happen in VA.  Is there anyway that I can make the network analysis process write to a list table in the report.  I've tried various filters, data mappings and joins but haven't been able to create the list table that the client wants.     Doing the work in Viya VA seems to be the better option as running the SQL code to produce the recursive macro to create the table in SAS 9.4 takes hours with my current code. Another option may be to run a stored process in VA - but I haven't tried this yet and am not sure if it's practical or even possible.   Thanks ... View more

Hi   I have a large SAS EG project that creates, reads and joins 12 tables from a variety of sources.  Occasionally one of the tables that is joined in the final SQL stage has zero rows, but normally all tables have at least one row.  When SAS SQL sees the table with zero rows it throws an error stating that tables must have more than zero rows - but I am unable to control for that with the basic input tables.   I'm thinking to insert a dummy row into the problematic table if there are zero rows, to allow for the project to run through to completion.  This would enable the join to occur without actually generating any additional rows of data.     I have two questions that I would appreciate assistance with: 1. is there a better way to prevent this error? 2. how should i place a dummy row into a table (whether or not it has existing data)?   Thanks for any suggestions.   Doug ... View more

I have created output for a customer that has (in very simple form): id1 vara varb varc vard vare id2 varf varg varh. id1 has a numeric value of, say 42, id2 will always have a value of id1+1 (i.e. 43 in this case). vara-varh are character values.   i now need to split each row into based on the values of id1 and id2 to be: id vara varb varc vard vare varf varg varh 42 ........ 43........   I've been trying to read id1 and then say if id2 =id1+1 then output but I still don't get the split right. I've been thinking how to force SAS to stop reading the line after vare then to drop down to the next line but don't know how to do that.   I don't want to go back to square 1 and start the job again as i think the customer may want the data in the original format once they test it.   Any assistance or tips appreciated.   Gordon ... View more

I'm a little bit stuck with a request from my boss who wants to know how long it takes a customer to spend 75% of a credit voucher.  The customer can use the voucher many times for many variously priced goods but she wants the time period that it takes to meet or exceed 75% of the value. Vouchers can be for any amount but are normally in the non-rounded hundreds to a few thousands (i.e $543.00 may be a value).   I was planning to firstly get the value of each voucher, then to begin adding up the purchases until the sum is equal to or exceeds 75% of the voucher value, then taking the date when that happens and subtracting the issue date of the voucher (or the date of the voucher's first use).  Not sure how to go about the coding yet but conceptually I'm looking to try something like (plain text version as opposed to an attempt to code): eg, for a voucher that is $1000.00,   array(date_of_purchase) _temp_; do I = 1 to (total number of purchases on a voucher); running_value=_value +purchase_value do until (running_value =1000 *0.75); Number_purchases=I; duration=date_of_purchase(I) - voucher_issue_date; end; end;   The data are structured in SAS with each row having a customer id, date of purchase, value of purchase and an indicator if the purchase is from a voucher (as well as the goods, quantity etc.)     Has anyone done anything similar that they could please share with me?  My boss knows that this is likely on the verge of my skill set and that I'm seeking help to get this done ("it's a learning experience!"). I'm not sure if this is a simple process that I am over complicating.   Thanks   Gordon     ... View more