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.   ... View more

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.   ... View more

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? ... View more

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.     ... View more

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. ... View more

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;   ... View more

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   ... View more

@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? ... View more

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[*]);         ... View more

Turn on MPRINT option and you can see you are making a common mistake with your use of LAG() functions. You are running them conditionally.  In particular this block of code. else do; &var._fs&avgback._long= (&alpha1.)*&var.+(1-&alpha1.)* (mean(&avgbackline_long.)); end; The macro varaible AvgBackLine_Long appears to have been constructed as LAG1(..),LAG2(..),....LAGn(..).   For the LAG() function to know what the previous value was you have to pass it the previous value.  By skipping the call to the LAG() function sometimes those values are not pushed onto the stack and so cannot be popped back off it later.   You might be able to fix your code by just changing that IF/THEN/ELSE block into something like this: &var._fs&avgback._long= (&alpha1.)*&var.+(1-&alpha1.)* (mean(&avgbackline_long.)); if _ind_<=&avgback. then &var._fs&avgback._long= &var.; But there is probably a much simpler way to code your algorithm using arrays. ... View more

You need to state what GUI interface you are using that is captured in your picture.   One of the responses is guessing it is Enterprise Guide. ... View more

You could also just generate two datasets and then merge them together. Create a unique id to perform the merge with, let's call it ID. To make the named input happy define variables for all of the named fields on the JOB= lines. data main jobs(drop=name lastrun); infile cards truncover ; input @; if _infile_ ^=: 'JOB=' then do; id+1; n=0; input name $ 1-8 lastrun $ 63-66; output main; end; else do; n+1; length job $8 schid $3 vrsn $10 leadtm $2; input (job--leadtm) (=) ; output jobs; end; cards; FREDFRED 004 FREDFRED FMS 001 ALL *NONE* YES 000 000 1732 BARNEY 004 BARNEY ABZ 001 ALL *NONE* YES 000 000 1341 JOB=JOB00001 SCHID=000 VRSN=09063/1602 LEADTM=08 JOB=JOB00043 SCHID=000 VRSN=94322/1613 WILMA 004 WILMA TRE 001 ALL *NONE* YES 000 000 0000 JOB=STEP9999 SCHID=000 VRSN=07263/0650 LEADTM=99 BETTY 004 BETTY KIL 001 ALL *NONE* YES 000 000 2351 ; data want; merge main jobs; by id; run; Result ... View more

Your math is off. Since the length of the macro variable is longer than the character variable you created in the data step you are seeing truncation. 98 %put %length(&allv_Y); 3171 The maximum length of a character variable is 32K bytes. The maximum length of a macro variable is 64K bytes.   Note that the string will be longer once you add those extra characters.   If you expect to do this for lists that approach the limit of a character variable you might want to switch to a pure macro code solution.   If you expect to need to do this for a list that could exceed the length of macro variable you might want to switch to a solution that keeps the list as multiple observations in a dataset instead and then use a data step to write the generated code to a file.   One way around the macro variable limit is define an actual MACRO instead.   For example if you have a dataset names HAVE with a character variable named VAR with values like: data have; input var :$32. @@ ; cards; LAKOACH AB_PA_IND_bad_ANYBAD AB_PA_MKR_TASHLUM AB_PA_AGE_VETEK_MASHK AB_PA_Y_TAS_SUMLLL_TYPE AB_PA_BITCHONOT AB_NEWC_Y_NIZHOS AB_NEWC_Y_NIZMISGOSH_HARIGA AB_NEWC_AKM_HZR AB_NEWC_MASHKANTA AB_NEWC_INC_COMPONENT AB_NEWC_INC_TREND ; You could run a data step like this to create code that would define a macro name VARLIST. filename code temp; data _null_; set have end=eof; file code; if _n_=1 then put '%macro varlist;' ; put var @; if eof then put / '%mend;'; run; %include code / source2; That you could then call where you need that list of names to appear. data new; length %varlist 8 ; call missing(of _all_); run;   ... View more