About Tom

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.

Tom

Excel stores datetime values as number of days since 1900. It stores the time of day the fractional part (number of seconds since midnight divided by number of seconds in a day). You can then use the formatting style to display these numbers in different ways. So I made new XLSX file and create three column headers in the first row: DATETIME, DATE and NUMBER. I then and typed "12/12/2025 08:41am" into the first row under the DATETIME column header. And copied the same value into the other two columns. I then used the Formatting menu item to change the style of the DATE column to YYYY-MM-DD and the NUMBER column as just plain number. This is what it looks like Google Sheets. Then I ran this SAS program to use PROC IMPORT to read in the first sheet from the XLSX file and display the values. First I displayed the values using the format that PROC IMPORT attached to them. Then I displayed them using the BEST32. format instead. Results: 71 proc import file='~/date_formatting.xlsx' dbms=xlsx out=test replace; 72 run; NOTE: The import data set has 1 observations and 3 variables. NOTE: WORK.TEST data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 73 74 data _null_; 75 set test; 76 put (_all_) (=); 77 put (_all_) (=:best32.); 78 run; datetime=12DEC25:08:41:00 date=December 12, 2025 number=46,003.36 datetime=2081148060 date=24087.36181 number=46003.36181 So PROC IMPORT noticed that the third column was just a number, so it copied as it was, but attached a format that displayed with with thousands separators. The second column it noticed that it was being displayed as a DATE value so it adjusted the number of days to reflect SAS's use of a different base date (SAS counts days from 1960). Notice that it did not remove the fraction of a day that 8:41am represents. The SAS date type display formats will ignore that fractional value. And for the first column it noticed that it had a datetime type display format, so it converted the number into a SAS datetime value (number of seconds since 1960). On the SAS side if you want to convert DATE to match DATETIME you could multiple it by the number of seconds in a day. (Note: no need to figure out what number that is, just use '24:00't ) Or you could use the DHMS() function and just put the DATE value (which includes the fraction of a day) as the D value and use zeros for the H,M and S values. To convert NUMBER into a SAS date you would need to change the base value. So just add '30DEC1899'd. The reason that you don't use '01JAN1900'd or '31DEC1899'd is because (1) SAS counts from 0 and Excel counts from 1 (2) Excel decided to mimic the over simplication that LOTUS 123 used and treats 1900 as a leap year, so it has second extra day (unless the value is before 01MAR1900). You might want to use FLOOR() to remove the fraction of day caused by the 8:41am. To convert the NUMBER into SAS datetime do both. First add '30DEC1899'd to convert it to a SAS date and then convert the SAS date into a SAS datetime.

Tom

Your original code was for some reason remembering the first value per by group, so I had copied that. But it does not actually appear to be needed. If you want to run the macro multiple times using different weights or lags just write the macro so that it makes a unique variable name. Or have it write unique output datasets. If you do the former then to get all of the new variables in the same dataset you can chain the calls. Have the second call use the output of the first call as the input. If you do the later then just remerge the datasets.

Tom

I don't understand why you would think that the same URL could be used for both POST and FETCH commands? Do you have documentation on the interface for the URL you are connecting to?

Tom

Not sure about Snowflake but in other databases that allow temporary table they usually appear in a separate schema. Usually based on the username of the database user. Once you figure out if that is true for Snowflake and what schema it uses try adding the SCHEMA= dataset option to your SAS code that tries to use the bulk load options. And if you do want to try to make two librefs see if you can make them using CONNECTION=GLOBAL on both libref definitions.

Tom

To use the previously calculated ema_fs values just change this line from: _lag[mod(_ind_,&avgback)]=&var; to _lag[mod(_ind_,&avgback)]=ema_fs_&var; Do you mean a double transpose? Transpose from long to wide, then try to run your algorithm using the wide dataset, then transpose again from wide to long? Sounds like that would just make the problem harder to solve.

Tom

Sounds like you want to use DATA from another dataset, not the LABEL attached the variables in that dataset. Converting your metadata data step into code is trivial. filename code temp; data _null_; set labels_info_tbl; length nliteral $65 ; nliteral=nliteral(var_name); file code; put 'label ' nliteral '=' label :$quote. ';' ; run; Now you can use %INCLUDE to run those generated LABEL statements where ever you need them. In your data step to make the dataset. data have; %include code / source2 ; input custID Wealth education; cards; 111 30000 15 222 7000 12 333 350000 21 ; Or in a PROC DATASETS step to modify the labels in an existing dataset. proc datasets nolist lib=work; modify have; %include code / source2; run; quit;

Tom

If you can't get the SAS admin to tell you what you need you might ask one of the SAS users to use the code in the answer to this thread to extract the information on the application servers from the metadata server. https://communities.sas.com/t5/Administration-and-Deployment/How-to-extract-SAS-Management-console-information-SASApp/td-p/846988

Tom

@andre_young wrote: thanks Ahmed, I have verified the JDBC URL I am using against the connection example in the cookbook, and it appears to be in the correct format. But are you still using the URL that points to the Metadata server instead of the server that can actually give you data?

Tom

I do not understand what you mean by "reverse transpose". I do not understand what you mean about the relationship between WT and LONG. Your SAS code appears to calculate WT as 2/(LONG+1) which would imply setting LONG to 10 would result in WT being 2/11. Note that by using the circular array you really only need to do a special case for the FIRST observation in each group. When there are less that N values in the array the MEAN() function will ignore the missing values. if first.&byvar then ema_fs_&var=&var; else ema_fs_&var = alpha*&var + (1-alpha)*mean(of _lag[*]);

Tom

Try something like this: data _temp; do grp=1 to 4; do i=1 to 100; x=sin(i/10.0); output; end; end; run; %let ds=_temp; %let var=x; %let byvar=grp; %let long=0.1; %let avgback=3; data step1; retain alpha %sysevalf((2.0/(&long.+1))) ; do _ind_=1 by 1 until(last.&byvar); set &ds; by &byvar.; where not missing(&var); retain _initial_&var; array _lag [0:%eval(&avgback-1)] ; if first.&byvar. then _initial_&var=&var; if _ind_<=&avgback then ema_fs_&var=&var; else ema_fs_&var = alpha*&var + (1-alpha)*mean(of _lag[*]); output; _lag[mod(_ind_,&avgback)]=&var; end; *drop alpha _lag: ; run; title "ema_fs_&var lag &avgback outcome"; ods layout gridded columns=2 rows=2 advance=table; ods graphics /width=480px height=300px; proc sgplot data=step1; by grp; series x=_ind_ y=&var/ lineattrs=( color=blue thickness=2 pattern=solid); series x=_ind_ y=ema_fs_&var/ y2axis lineattrs=( color=red thickness=2 pattern=solid); run;quit; ods layout end; The use of permanent variables for the ARRAY and placing the SET inside the DO loop means that the arrayed variables are automatically reset to missing at the start each BY group. Using an array that is indexed from 0 to N-1 instead of from 1 to N makes it easier to use the MOD() function to indicate which index in the array needs to be replaced with the current value. Note: I don't think you want to exclude the missing values. That would mess up the _IND_ variable you later use as your X axis.

Online Status	Offline
Date Last Visited	yesterday