About gordononline

gordononline · ‎08-11-2020

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

gordononline · ‎04-14-2020

Thanks but this doesn't seem to work as SAS proc sql doesn't have 'outer join' as a statement. Likely T-SQL allows this though. Doug

gordononline · ‎04-14-2020

Hi The code below identifies id and state where there is a match in the combinations but how does one do the inverse and find the ids and state combinations from the first table that don't have a corresponding match in the second table? I've tried where id = id and state not = state, tried a correlated subquery and a range of coding attempts that give me errors. I have a work around that works at present (that relies on me knowing all available values, concatanating them and doing a case statement with a find function) but I'd like to be able to write this dynamically as there are a host of uses for this in my area. Here's an example: data test; input @1 id 1. @3 state $3.; datalines; 1 vic 1 nsw 2 nsw 2 qld 3 wa ; run; data pwd; input @1 pwd 2. @4 id 1. @6 state $3.; datalines; 11 1 nsw 12 1 vic 13 2 nsw 14 2 vic 15 3 wa 16 3 qld 17 3 sa ; run; proc sql; select * from work.pwd as t1 inner join work.test as t2 on t1.id=t2.id and t1.state=t2.state; quit; This gives me all the matching combinations. However what I'm after is: id state 2 vic 3 qld 3 sa (i.e. all non-matching combinations). Not fussed if this is done in a data step or proc sql but would prefer sql as my team is mainly a sql shop. Thanks Gordon

gordononline · ‎04-10-2020

Here's my partial solution: data test; input @1 id1 2. @4 a1 $1. @6 b2 $1. @8 c3 $1. @10 d4 $1. @12 e5 $1. @14 id2 2. @17 f6 $1. @19 g7 $1.@21 h8 $1. @23 i9 $1. @25 j10 $1.; datalines; 19 a b c d e 42 g h j k l 34 e r t y u 54 q w e r t 57 a s d f g 66 q w e r t ; run; data new1; set test; output; run; data new2; set test; drop id1; rename id2 =id1; output; run; data new3; set new1 new2; drop id2; run; proc print; run;

gordononline · ‎04-09-2020

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

gordononline · ‎03-31-2020

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

Online Status	Offline
Date Last Visited	Saturday

proc sql error - table has zero rows

Re: How to chose items that don't match

How to chose items that don't match

Re: Split a row based into two rows

Split a row based into two rows

Determine the time that it takes a customer to spend 75% of a credit v...