@fnajera If you don't get an answer here, contact SAS Tech Support. They can give you an official answer where things are at. ... View more

@Nasser_DRMCP wrote: hello thanks for your respons. I tested what tom has suggested. and the 2 codes take the same time, arroud 10 min   nasser And that's o.k. for you or not? Make sure you look into the readbuff value. If it's set to the default of 1 then increasing this value will highly likely improve elapsed time further. And depending on your data and disk I/O adding compress=yes could also help. ... View more

Your RegEx needs some amendment. Below should work. data test; infile datalines4 truncover; input line $256.; if prxmatch('/^\%let etls_\w+.+;$/i', strip(line)) then delete; datalines4; %let etls_startTime = %sysfunc(datetime(),datetime.); %let etls_recordsBefore = 0; %let etls_recordsAfter = 0; ;;;; But as @MrSantos already advised, you should use the SAS  Content Assessment Tool with it's migration tools.  What you're doing would only make sense if you wouldn't want to migrate to SAS Studio flows but to simple .sas programs ...but this would only make sense imho if the SAS Viya version you've licensed doesn't allow for the direct migration to SAS Studio Flow.  ... View more

In case the data step you shared is within a macro, the macro variables created could be in local scope.  If that's the issue then try with below amendments.     ... View more

... should I make a join between the two big vertica tables ? or should I , first, create a sas table filtered on model and the sent month. and join this sas table on the second vertica table ? With both tables in Vertica you certainly should push the join to the database to reduce the data volume before it gets transferred to SAS. I'd be going for an inner join like @Tom already proposed and test with a single model. Once that performs sufficiently, a single query with all the models at once will highly likely return the best overall performance.   You can write this SQL explicit (using Vertica SQL flavour) or implicit (using SAS SQL flavour). If using implicit SQL you need to ensure to only use syntax that SAS can get fully convert to Vertica SQL as else SAS will need to first pull the data to the SAS side (which certainly will have a significant impact on performance).    And for Vertica: If you know how the data is distributed over the nodes, then it could also make a significant performance difference how you formulate your join condition. The less data Vertica needs to move between nodes, the better it will perform.    Also make sure that you set libname option readbuff to something like 10000 (or even bigger depending on memory available). The default is 1 meaning Vertica will send only 1 row at a time to SAS.   If you share your current SQL syntax with us then we can eventually provide further guidance. ... View more

Based on some Copilot Q&A please try below and let us know if this works for you. Select TableName, CreatorName, CAST(CreateTimeStamp AS VARCHAR(26)) AS CreateTimeStamp, LastAlterName, CAST(LastAlterTimeStamp AS VARCHAR(26)) AS LastAlterTimeStamp, AccessCount, CAST(LastAccessTimeStamp AS VARCHAR(26)) AS LastAccessTimeStamp from DBC.Tables Where upper(DataBaseName) = 'DWP_VALL' And TAblename = 'VBM374_USED_BRANCH_CUSTOMER' ... View more

It appears that libname teradata is pre-assigned. If you've got SMC or SAS DIS then you could look into the library metadata definition. You can also run libname teradata list; to get more information.   I personally prefer using a libname over your first way because it can be used directly within any Proc or data step. But of course sometimes it's advantageous to use explicit teradata SQL that allows you to better reduce data volumes on the Teradata side. The following is a variant to your first way but uses the libname definition. libname mytera teradata .....; proc sql; connect using mytera; create table WAY1 as select * from connection to mytera ( select top 5 * from VBM374_USED_BRANCH_CUSTOMER ); disconnect from mytera; quit ; In your sample case where the libref is teradata it would of course be connect using teradata; and you also wouldn't need the libname statement because the library is already assigned. ... View more

Assuming you're running whole flows with multiple programs, the traditional approach would be to determine the critical path, identify the longest running program on this critical path and in there the longest running steps. You can often get quick wins just by tweaking these long running steps.  Run your programs with option fullstimer which provides additional info that's important for choosing a good performance tuning approach. ... View more

We can't know what your SAS scripts like HYFI_INTExpSF.sas are actually doing but if the create text files then it's highly likely that they use a SAS table as source to create these text files.  As others already mentioned Proc Compare works on SAS tables and not text files. If your current scripts create these text files as a 1:1 export from SAS tables AND if these SAS tables are permanent, then you could of course write another SAS script that uses Proc Compare and compares the source SAS tables.    ... View more

Like @SASKiwi already mentioned a difference in the first letter is normally relevant and the match codes need to be different. If you have cases where they should be the same then you need to learn how to amend/extend the out of the box QKB to cover your special cases. This should become a BAU task if using SAS DQ in earnest.  ... View more

@hrmannan  Sorry, but I've changed my laptop since this download and didn't keep the code.  ... View more

@Ksharp  Neat. Not sure if I was ever aware of syntax like (-7 in x). That's certainly useful. ... View more

In the SAS docu under Numeric Precision some more detail for what @Tom already explained. ... View more

Below option should work even if the sum of all q vars should result in zero (obs 7 in sample data). data have; length flag_want 8; infile datalines truncover; input (q1-q20 flag_want) (:best32.); datalines; 20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 20 20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 20 0 -7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 -8 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 . -8 -7 -7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 . -7 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 ; run; data want; length flag_derived 8; set have; array a_q (*) q:; do i=1 to dim(a_q); /* 1. if any one of the 20 variables > 0 then flag=1 */ if a_q[i]>0 then do; flag_derived=1; leave; end; /* 2.else if any one of the 20 variables = -7 or -8 or -9 then flag=. */ else if a_q[i] in (-7,-8,-9) then flag_derived=sum(flag_derived,2); /* 3. else if ALL of the 20 variables = 0 then flag=0 */ else if a_q[i]=0 then flag_derived=sum(flag_derived,0); end; if flag_derived>1 then call missing(flag_derived); drop i; run; proc print data=want; run;   ... View more