One possibility would be to try the update first, and check if any observations were updated: %if &location ne _ALL_ and %length(&locid_text)=0 %then %do; update sas_gbl_input_parm set htl_cd = "&location" , parm_val = "&value" , current_ind = 'Y' , lst_updt_usr_id = "User_1" , lst_updt_ts = datetime() where parm_nm = "&parm_nm" ; %if &sqlobs=0 %then %do; /* no records updated */ insert into sas_gbl_input_parm set parm_nm = "&parm_nm" , htl_cd = "&location" , parm_val = "&value" , current_ind = 'Y' , lst_updt_usr_id = "User_1" , lst_updt_ts = datetime() ; %end; %end; ... View more

Another possibility is to use PRX functions, which enables you to check for numbers in the right places: data want; set m; length pr_id $5 pr_name $40 pr_qty $5; prxid=prxparse('/^(\d+) (.+) (\d+)\s*$/'); if not prxmatch(prxid,info) then error 'No match'; else do; pr_id=prxposn(prxid,1,info); pr_name=prxposn(prxid,2,info); pr_qty=prxposn(prxid,3,info); end; keep info pr_:; run; The PRX string searches for beginning of string "^", some digits "\d+" which are placed in the first capture buffer (the "()" around), a blank, a string which can be anything (".+", in the second capture buffer), a blank, some digits in the third capture buffer, and finally some whithespace "\s*" and end of string "$". You then use PRXPOSN to get at the capture buffers.   ... View more

Sounds like the format library APFMTLIB contains an informat named Z, which is assigned to one or more variables on the input table. Zw. may be a standard format, but that does not stop you from creating an informat with the same name.  ... View more

The problem may be that the SQL does the following, exactly because you have an index on the history table: Unique customer numbers are extracted from X. OK, takes very little time as X is small. For each customer number in X, the corresponding record is looked up using the composite index. Would be the fastest method with just a few (or a few hundred) records, but not with a million (if the history table is not sorted by the index variables, the lookup would be jumping from one disk segment to another all the time, meaning that the whole table may be read from disk many times over). If you do things with a datastep, you can turn the process on its head, so that customer numbers are looked up in the small table instead, e.g.: data customer_history; modify costumer_history; where current_flag='Y'; set x(keep=customerno) key=customerno/unique; if _iorc_ then _error_=0; /* not found, reset _error_ to avoid error message */ else remove; /* delete the record from history */ run; The idea is that the X table index hopefully is small enough to fit completely in memory. An alternative is to load the X table to a hash table, which executes slightly faster than the SET with KEY=, but on the other hand takes time to load. As I assume that you are only deleting one record for each value in X, I think the solution shown above is the fastest when X is already indexed. ... View more

The problem about using both '#' and '|' as delimiters is that you may find the odd '#' somewhere in a text field in the data section. In which case the rest of the line input goes completely awry. I actually considered that when writing my own answer (to which Tom had an excellent amendment), but discarded the idea. Been there, done that... ... View more

Tom, I did not know that you could put a variable in the delimiter options, and then change it. Very valuable, Thank you! ... View more

The easiest is probably to change the first space to a "|": Data members(keep=statement_id member_no) addresses(keep=member_no whats_this title) payments(keep=member_no premiums rebate) days(keep=member_no hospital_days holidays) ; infile cards dsd delimiter='|'; length linetype $30; input @; prx_id=prxparse('s/ /|/'); call prxchange(prx_id,1,_infile_); input linetype @; select(linetype); when('#MEMBERSHIP_PERSON#') do; input statement_id member_no; /* add the other variables as needed */ output members; end; when('#ADDRESS_LINE#') do; input whats_this title $; output addresses; end; when('#PAYMENT_LINE#') do; input premiums rebate; output payments; end; when('#MLS#') do; input hospital_days holidays; output days; end; otherwise input; end; retain member_no; cards; #FILE_HEADER# MOnth|1901124578965 #MEMBERSHIP_PERSON# 9999944999|0009999|05|ABC|F|Medium|REGN|||07/Jun/2017|Pay Pal|||N|DISTIBUTOR|CURRENT|01|Y|Annual|2017|EXISTING|01/Jan/1901 #ADDRESS_LINE# 0059999999|Mr|Test||Test||M|03/Jan/1901|009999900|Online|abc00@abc.com.au|N|N|00244444||11 ABC Street|||SUBURB|3003|SA|Australia|N|N|00999900|Email|abc00@abc.com.au|N|N|||||||Y|Y|0000000001|YZx|0000000001|ZYX|PERSON|N|0000000000||||||0000000000||||| #PAYMENT_LINE# 1234|123|35|First Last #PAYMENT_LINE# 123|123|12|First Last #MLS# 123|123 #SOC_YHC# 00099999|First||Last|01|03/Jan/1901|01/Jan/1901|30|00000|01/Jul/1901|00|C|01/Jul/1901|Y| #SOC_YHC# 00099999|First|F|Last|02|03/Jan/1901|01/Jan/1901|30|00000|01/Jul/1901|00|C|01/Jul/1901|Y| #SOC_MEMBERSHIP# Mr|First||Last|12 ABC Street|SUBURB SA 3003|||3003|C|Couples|996|PRODUCT||01/O1/1901|30.256%|225|211|145 #SIS# N ;run; I used CALL PRXCHANGE for this, because it changes the string in place (which is faster than calling a function), and because it allows you to specify that you only want to change one occurence. I output several datasets, as there obviously can be more lines of some types per person (e.g. payments). The key variables (in this case I assumed member_no) should then be on all the tables, and they should be retained (they must be in the first row read for that member, I assume that is always the membership-person line). It should be reasonably easy to fill out the remaining line types and variables. If you are wondering what the WHATS_THIS variable does, that is just a placeholder, because your data do not fit the specification. Title should be the first variable in the address line, but there is some undefined number first. ... View more