About Tom

Tom

You need to explain WHY that code works.

Tom

@dylanleong78 wrote: thanks a lot for this! sorry it took me awhile to respond. i wanted to make sure applying this logic in my full implementation works. Note that there is a lot of simplification in the code I posted. When using UNTIL() there is no need for an extra test to insure the DO loop executes at least once. You can add an UNTIL() (or a WHILE()) clause to a normal iterative DO loop. So there is no need to increment your loop counter on your own. You can use CAT... series of functions to simplify building strings from other strings and even numbers. And a comment on comments. If you place the comments that explain the code BEFORE the code instead of after it then it is much easier to understand the code. Plus it will keep you from making lines that are too long for humans to read. With long lines in addition to scanning down the page you have to keep sweeping the field of focus left and right.

Tom

@dylanleong78 wrote: thanks. i tried putting short_code=strip(upcase(substr(nam_1_s,1,2))); but it did not work. then i placed the strip in in_prev=index(last_tmp_cd, short_code); and that worked! The reason that putting the STRIP() function call in an ASSIGNMENT statement did not help is because the trailing spaces were added back when the value was stored into the fixed length character variable.

Tom

Most likely the issue is using INDEX() when you actually want INDEXW() or better still the FINDW() function because of the TRIME modifier it allows. data have; input nam_1 :$50. iso_a2 :$2. ; cards; Ham AL Harriet AL Harry AL Hello AL Hitachi AL Hog AL Hogwarth AL Holler AL Hummer AL ; data want; set have; by iso_a2 ; length short_code $2 list $100 ; retain list; if first.iso_a2 then call missing(list); do index=2 to length(nam_1) until(not found); short_code=upcase(cats(char(nam_1,1),char(nam_1,index))); found = findw(list,short_code,',','it'); end; list=catx(',',list,short_code); run; What is it you want to do when you get to something where there is no unique second letter to use?

Tom

Read the documentation on the FIND() function, in particular look at the various modifiers that it offers. Your first example does not work because of the lengths of your character variables. Since you did not tell SAS explicitly what length to use for them it followed its internal rules for guessing what length to use. So STRING is defined as $17 since the first value you assigned to it was 17 bytes long. And CHAR_SUBSTRING was defined as $12 since the default width for the BEST format is 12. So that means FIND() was unable to located a 3 followed by 4 zeros and 7 spaces in "40000,80000,30000". Here are some things you could try: Use the TRIM(), STRIP() or even CATS() function to removing leading/trailing spaces. Use the T modifier in the FIND() function call. Note: Since you seem to be searching for words you might want to use the FINDW() function instead.

Tom

So others know when they are at risk can you clarify what is the length that causes trouble? For example is it names longer than 200 bytes? Or some other limit? Is it the length of the fully qualified filename? Or just the name of file itself? That is could you not rename the file but just move it to a higher directory so that the total name is shorter.

Tom

You appear to be capturing the return code from the function calls rc = metadata_getattr(loginUri, "UserID", userIdValue); , but you are not testing the return code to check if the function succeeded before checking the value of the data set variable. if lowcase(userIdValue) = lowcase("&user_id") then do; Perhaps the call is failing and this is the cause of the false hits?

Tom

Talk to Microsoft about changing the behavior of Windows?

Tom

Do all of the files use the same structure? That is they have the same variables in the same order? If so then yes you can read multiple files as if they were one. For example you can use a wild card in the INFILE statement. You can use the FILENAME= option to get the current file's name so you can skip the header records. Plus if you need to pull out the suffix on the filename as a variable you can do that. So for example if each file has 4 variables, two of which are character strings and one is a date and the last is a plain old number then the code might look like this: data want; length filename $256 suffix $8 ; infile 'PRJ__20221201_13227_*.csv' dsd truncover filename=filename; input @; if filename ne lag(filename) then delete; suffix=scan(filename,-2,'._'); length c1 $10 d1 8 c2 $3 n1 8; format d1 yymmdd10.; informat d1 mmddyy.; input c1--n1; run;

Tom

If you have one observation per VISIT your SQL should work. Otherwise if you have unique visit ID you could count the number of distinct visits where the condition is true by nesting a CASE clause inside the aggregate function. count(distinct case when cause_any_inj then visitid else null end ) as cause_inj_cnt And using just BY group processing in a data step will not remove any observations, unless you have a DELETE statement, or a subsetting IF statement, or a conditional OUTPUT statement. Can you share a simple example dataset (does not need to be the real data) and the expected result for that input.

Tom

It is not clear what you are starting with since the code you shared cannot run. If the variable you named DATE is an actual SAS date variable then it is NUMERIC. In order to create a CHARACTER variable you will need to use a different variable name. And if it is an actualy date variable it cannot contain any partial date values, so you can ignore the SDTM specifications for how to represent partial date values. To convert a date value into a character value in the style YYYY-MM-DD you can either the YYMMDD10. format, Or the YYMMDDD10. format, the extra D stands for DASH. Or use the E8601DA10. format. data want; set db; charvar=put(date,yymmdd10.); run; If your variable is character and looks like the text in your example then you don't need to do anything since it is already in that style.

Tom

The macro variable probably has a different value when you run the task than it had when you ran the test. This is mentioned in the documentation. https://documentation.sas.com/doc/en/egamotasks/8.3/n08ksbwyrsc9mxn0zpgenjnp5ejb.htm CAUTION The macro variables must exist within your SAS session (either as SAS built-in macro variables or defined in an earlier process). If the macro variables are not specified correctly or if you use incorrect syntax for a macro function, the Copy Files task can generate errors or unexpected results. To verify that the macro expressions resolve correctly, click Test macro values. A short SAS program runs to resolve the macro expressions, and the results are displayed in a new window. Remember that when you test these values, the macro expressions resolve to values that reflect the current state of the macro variables or the results of any macro functions that you use. These values can be different when you actually run the task later. Try adding a Code step in your process to display the value of the macro variable just before it tries to run the Copy Files step.

Tom

Your table does not have the variable AV that you referenced in the expression using the CONTAINS keyword/function. I suspect you meant to use the string 'AV' instead. You should use the FINDW() function instead of using CONTAINS to avoid changing part of name that just happens to have AV in it. For example "BRAVO STREET". Or perhaps look at using regular expressions. PS: Please post text, such as code or LOG lines, as text and not pictures. You can use the Insert Code button to get a pop-up window where you can paste or edit the text.

Tom

@Callam1 wrote: Thank you for this. One question regarding the if ‘first.’ line below, given I want pay to re-set to start_pay if the person or employer or tax year changes, should I specify instead: if first.personID or first.employerID or first.tax_year_start then pay=pay_start; Is it wrong if I specify all 3? You won't get the wrong answer if you specify all three, but you are making the data step test things it does not need to test. FIRST.PERSONID means this is it the first observation for this value of PERSONID. FIRST.EMPLOYEEID means this is the observation for this value of EMPLOYEEID for this value of PERSONID. FIRST.TAX_YEAR_START means this is the first observation for this value of TAX_YEAR_START for this value of EMPLOYEEID and this value of PERSONID. So if FIRST.PERSONID is true then FIRST.EMPLOYEEID is true and FIRST.TAX_YEAR_START is true. And if FIRST.EMPLOYEEID is true then FIRST.TAX_YEAR_START is true. You want to test groups defined by the value of all three variables. So you only need to test the innermost BY variable's FIRST. flag.

Tom

Yes. But it looks like you already did that, you just wrote the logic in a confusing way. Instead do as Copilot says and add a BY statement to the DATA step and use the FIRST. flag that SAS will set automatically instead of trying to calculate if it is the first observation in the group on your own. Note that in addition to setting the various FIRST. and LAST. flags the BY statement will also insure that the data step fails if for some reason SORTED_DATA is not actually sorted in the way the BY statement says. Your step would instead happily generate gibberish values for PAY in that situation. Also note that you should use the DIF() function instead of the LAG() function. That will not only save some typing but also make the meaning of the code clearer and eliminate the nasty note about missing values. 71 data test; 72 set sashelp.class; 73 age1=age-lag(age); 74 age2=dif(age); 75 run; NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 1 at 73:11 NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: The data set WORK.TEST has 19 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds So your code should look like this: proc sort data=sorted_data; by personID employerid tax_year_start payment_date; run; data pay_data; set sorted_data; by personID employerid tax_year_start payment_date; pay = dif(cum_pay); if first.tax_year_start then pay=start_pay; run; Also note that I just assumed you know what your data is and that the variable START_PAY has the value you want for PAY on the first observation per PERSIONID, per EMPLOYERID, per TAX_YEAR_START. If you don't actually have such a START_PAY variable then look and the data and see if CUM_PAY might already be resetting for each new TAX_YEAR_START so that it can be used to set PAY.

Online Status	Offline
Date Last Visited	Friday