Patrick Tracker

Re: SAS CDE creates SAS datasets with restrictive file permissions on SAS Grid environment

Patrick — Sat, 15 Aug 2026 02:40:31 GMT

umask allows to define the maximum permissions, ACL's allow to define the minimum permissions.

For what you want, you could also on the top folder define an ACL for the desired minimum permissions and the setgid bit for the required group. Make sure you inherit this down for the files and folders that already exist under this top folder. Any newly created folder or file should then get these defined permissions and group independent on how the folder or file gets created.

Re: Capture SQL View Definition for Programmatic Change

Patrick — Sun, 28 Jun 2026 05:49:30 GMT

@Quentin The views in the target environment must point to the replicated data in the target environment. For this reason they must get re-created.

This environment with replicated production data is then what one can use to create other environment for various Dev & Test purposes and there one can then create links to the replicated data for anything file that doesn't need write access during the Dev or Test activities.

Hope that makes sense.

Re: Capture SQL View Definition for Programmatic Change

Patrick — Sat, 27 Jun 2026 03:51:54 GMT

Sorry for answering so late. I had a few rather busy weeks.

@Tom Unfortunately things are still changing and also inconsistent. This metadata driven approach is only used for a portion of the view creation. Some views are also created directly fully coded spread out over a lot of scripts. And there is talk about getting rid of the metadata driven approach at all. That's why I'm aiming for an approach that doesn't rely on the code that created the views.

@Quentin Symlinks are certainly not the solution. A view can be something like select <subset of source columns> from blah.table using libname blah "path to folder". I believe the only way is to re-create the view with a different "path to folder".

Re: Difference between dates in SAS doesn't match results

Patrick — Thu, 11 Jun 2026 11:01:46 GMT

Not knowing much about R syntax, what Copilot tells me sounds very convincing.

I'm not going to post more AI answers I can't validate, but I'd suggest you use an AI, provide the SAS code and ask it to replicate this in R.

Re: Capture SQL View Definition for Programmatic Change

Patrick — Thu, 11 Jun 2026 10:15:13 GMT

Hi @Kurt_Bremser

Thanks for answering.

There are no explicit scripts. The views get created metadata driven. Below an example of the main "table" for this.

What I'm trying to do, is to create the data environment for an integration environment using the data from source that has been defined as "source of truth" and against which data needs to get reconciled (after running for another month).

In theory I wouldn't have to bother about the views, because they should get re-created by such a run. ...but one can't be sure.

I could of course use the config Excels and the existing scripts from source to build some adhoc job for re-creating the views in the target environment, BUT... I really would like to avoid this. Things are done messy.
What I would like to do, is to create the data (views included) for this integration environment without any dependency on any version of Excel config sheets or scripts.

I hope this explains why I'm trying to extract and use the view definitions to re-create the views.

Regards, Patrick

Capture SQL View Definition for Programmatic Change

Patrick — Thu, 11 Jun 2026 08:50:30 GMT

Hi Experts,

I need to replicate a SAS 9.4 data environment in a Linux environment. Using rsync -av works like a charm for this.

The challenge I'm facing is that the source environment also contains SQL views in the form:

select *
from lref.table
using libname lref "<source_root_path>/<relative path>";

I need to re-create these views in the target environment by replacing the source_root_path with the target_root_path.

Do you know of any other/better way than executing a SQL describe view, capture the log output in a file, parse out the view definition, change the path and then use this new code to create the views in the target environment?

I couldn't figure out any other way to retrieve the view definition.

Thanks, Patrick

Re: Question from a DBA on idle database session

Patrick — Thu, 23 Apr 2026 03:36:47 GMT

@climbamnt What you shared here just helped me figuring out what went wrong with my process. AND even better: MS Copilot found your contribution and shared the link with me.

Re: SAS online on mainframe - Is there a way to prevent execution of a DELETE command via SCL code

Patrick — Sat, 18 Apr 2026 09:37:40 GMT

Copy/paste your question into MS Copilot 365.

I can't fully validate the answer I've got, but it looked sensible to me.

Be aware that SAS/AF, SAS/FSP have been discontinued under SAS Viya (as well as "PC SAS") and though whatever you build using this technology becomes "technical debt".

Re: Bulk loading

Patrick — Sat, 18 Apr 2026 09:16:27 GMT

@Wiki-Ben The screen shot looks like DI Studio. Correct?

I've asked Copilot 365 about performance when using bulkload=yes with SAS Access to ODBC and the answer was interesting (though don't know if correct):

So just based on the Copilot answers, it's likely better to go with what @SASKiwi proposed (increase the insertbuff value, set dbcommit=0).
If you are using SAS Access to SQL Server then of course bulkload=yes could might make a difference, especially when loading into an empty table.

Bulkload=yes can be a libname or even a dataset option and though I would assume setting it in DIS will be sufficient on any level.

How to improve performance copying a huge table while also creating additional indexes

Patrick — Sat, 14 Feb 2026 06:01:01 GMT

Hi all,

I'm dealing with a case where analysts/modellers are dealing with Base SAS analytical tables that can get up-to 2.5TB in volume (compressed) with up-to 1000 variables.

Obviously such volumes are not ideal for adhoc analysis especially because there aren't many indexes on these tables.

I can't do anything about these source tables and though what I'm thinking:

Write a macro that allows these users to create a copy of such tables under their user folder with ideally only a sub-set of columns and added indexes of their choice.

Because even reading a 2.5TB table also takes really long, I'm trying to come-up with a table copy approach that performs as good as possible.

The environment is a SAS Grid, SAS 9.4M5, rhel with 32 cpu's per node (actual). The default for an EG session the users spawn are 4 nodes.

Below demo code is the best I could come up with and I'd love your advise if there is anything else I could do to improve performance both for creating the copy as well as query performance using where clauses.

I assume for the copy and index creation process it's all about minimizing i/o and passes through the data.

For this reason, I've added creation of new indexes as data set option to the append base table ...but I don't really know if SAS will actually create these indexes via a single pass through the data together with the copy process or if creating the indexes later on via a proc datasets create index would perform the same.

I'm creating the target table using the spde engine. I don't have a lot of hands-on experience with this engine but I believe because the data copy needs to be under a user folder, there is no use for more than one data or index folder.

About the number of partitions: Based on what I've read in the docu, I'm thinking about defining them as "size of source tables/number of actual cpu". Not sure if I got that right though.

And here the sample code (self contained and fully executable as is).

options fullstimer msglevel=i dlcreatedir;
%let root_path=%sysfunc(pathname(work));
%let tbl      =analytical_table;

/*-- source table: existing analytical table with up to 2.5TB (compressed) and up to 1000 variables --*/
libname src "&root_path.\src";
data src.&tbl.;
  set sashelp.class;  
  array var_ {20} 8 ;
  do k=1 to 10000;
    do i=1 to dim(var_);
      var_[i]=i*_n_;
    end;
    output;
  end;
run;
proc sort data=src.&tbl. out=src.&tbl.(index=(name age n_a=(name age)));
  by name;
run;


/*-- target table: copy of analytical table with added indexes and reduced number of columns --*/

/* create required folders */
libname trg "&root_path.\trg"; 
libname trg "&root_path.\trg\spde_&tbl";

/* assign target library using spde engine */ 
libname trg spde "&root_path.\trg" datapath=("&root_path.\trg\spde_&tbl") indexpath=("&root_path.\trg\spde_&tbl") compress=char /*partsize=&gb_part_size.G*/;

/* create target table with added indexes and less columns */
proc datasets lib=trg nolist nowarn;
  delete &tbl.;
  run;
  append
    base=trg.&tbl.(index=(var_1 var_2 idx_01=(var_1 var_2)))
    data=src.&tbl.(keep=name--var_4)
    getsort
    ;
  run;
quit;

/* list indexes on target table */
proc sql;
  select *
  from dictionary.indexes
  where libname='TRG' and memname=%upcase("&tbl.")
  ;
quit;

Thanks, Patrick

Re: send email +attach log file only in case of error or warning

Patrick — Sun, 18 Jan 2026 00:57:44 GMT

@Kurt_Bremser @Tom
Thank you both for your explanations.

So if SAS doesn't catch all errors and return them as error condition to the shell then one can't trust any scheduled process flow (lsf, airflow, ...) where child nodes only execute if all parent nodes executed without error.

Do I get this right? And if yes how are you scheduling jobs?

Re: send email +attach log file only in case of error or warning

Patrick — Sat, 17 Jan 2026 03:22:06 GMT

@Kurt_Bremser Why did you have to scan the SAS log and couldn't just check for the exit code of the process?

@Ronein Please provide more detail how you run your daily job? Are you using a scheduler or are you just submitting the job every day? Which environment are you using (OS and potentially scheduler), is it a SAS grid or a single machine, ideally share your batch command with us.

Re: How to switch between different projects using EG?

Patrick — Wed, 07 Jan 2026 04:09:40 GMT

If you're talking about the server side SAS sessions then with EG you can only have one session at a time per server you connect to.

You can always disconnect/reconnect to the server to spawn a new session.

You can connect to different app servers (if available) with separate sessions and you can select on which app server to execute a program.

You can also have two EG projects open (=EG started twice) and they will create separate sessions.

Re: Download Dataset From SAS Server to Local Folder

Patrick — Tue, 06 Jan 2026 22:47:23 GMT

Below a Gemini answer when using Google Chrome that looks right to me:

Re: Need help with merging two different data sets

Patrick — Tue, 30 Dec 2025 00:41:25 GMT

Not really sure if that's the ideal data structure for your t-test but below what you've been asking for.

Please note that I've changed the year in the last row for data A so it matches what you show us as desired result.

data A;
    infile datalines dsd truncover dlm=' ';
    length TICKER $5;
    input TICKER $ YEARP BSIZEP;
datalines;
ACGN 2019 8
ACRS 2019 8
ADAP 2018 10
;
run;

proc sort data=a;
  by ticker yearp;
run;

data B;
    infile datalines dsd truncover dlm=' ';
    length TICKER $5;
    input TICKER $ YEAR DBSIZE;
datalines;
ACGN 2020 7
ACGN 2021 7
ACMR 2021 5
ACRS 2020 9
ADAP 2020 9
;
run;

proc sort data=b;
  by ticker year;
run;

data want;
  merge a b;
  by ticker;
  output;
  call missing(of _all_);
run;

proc print data=want;
run;

Re: Sub-String Theory: Reducing duplicate substrings to single substrings

Patrick — Tue, 30 Dec 2025 00:02:56 GMT

Below a possible approach.

data have;
  infile datalines truncover;
  input have_string $200.;
  datalines;
Los Lunas/Corona/Corona/White Mountain/White Mountain
;

data want(drop=_:);
  if _n_=1 then
    do;
      length _term $50;
      dcl hash h1();
      h1.defineKey('_term');
      h1.defineDone();
      
    end;
    
  set have;
  
  if 0 then want_string=have_string;
    
  do _i=1 to countc(have_string,'/')+1;
    _term=scan(have_string,_i,'/');
    if h1.check() ne 0 then
      do;
        want_string=catx('/',want_string,_term);
        _rc=h1.add();
      end;
  end;
  
  _rc=h1.clear();
 run;
 
 proc print data=want;
 run;

Re: Code to search for .zipx files in a Windows directory and export list to Excel?

Patrick — Thu, 18 Dec 2025 01:09:52 GMT

Can you run dos commands out of SAS in your environment? If you don't know then run below. If it shows XCMD in the SAS log then you can, if it's NOXCMD then you can't.

proc options option=xcmd;
run;

If you can run dos commands then something like below gets you the list.

/* list all .zipx files under c:\temp and sub-folders */ 
data work.my_zipx;
  infile 'dir "C:\temp\*.zipx" /S /B' pipe truncover;
  input myfiles:$1000.;
run;

And once you've got the files in a SAS table, you can use Proc Export or ODS EXCEL to write the table to Excel.

Or even "lazier" just write the found files to a .csv text file that you can open directly via Excel.

/* list all .zipx files under c:\temp and sub-folders and write records to C:\temp\zipx_files.csv */ 
data _null_;
  file "C:\temp\zipx_files.csv";
  infile 'dir "C:\temp\*.zipx" /S /B' pipe truncover;
  input;
  put _infile_;
run;

Re: Exam 000-240 – Am I Ready?

Patrick — Mon, 15 Dec 2025 00:19:12 GMT

It's long since I've done a SAS cert but at that time passing the practice exam was a very good indication how I'll be doing at the exam.

Re: LAG function bad for copilot...shall I trust copilot?

Patrick — Sun, 14 Dec 2025 02:20:49 GMT

What Copilot tells you is true for:

pay=cum_pay - lag(cum_pay);

But in your logic you then populate variable pay again with start_pay whenever the condition becomes true - and the condition will always be true if there is a different ID/employerID/tax year and though the issue Copilot flags won't come into effect.

if personid~= lag(personid)
   or employerid~= lag(employerid) 
   or tax_year_start~=lag(tax_year_start)
then pay=start_pay;

You could consider to express your logic as below.

data pay_data;
  set sorted_data;
  by personID employerid tax_year_start payment_date;
  pay=cum_pay - lag(cum_pay);
  if first.tax_year_start then pay=start_pay;
run;

Note: lag(cum_pay) needs to execute for every row and though can't get moved to an else condition.

Re: add single quotes to all macro var components

Patrick — Mon, 08 Dec 2025 07:44:28 GMT

The code I've posted here will also work for this macro variable. I wouldn't directly change the macro var itself but create a new one.

%let allv_y2=%sysfunc(prxchange(s/(\w+)/'$1'/,-1,&allv_y));
%put &=allv_y2;

And if you also want to replace multiple blanks with a single blank then below should do.

%let allv_y3=%sysfunc(prxchange(s/(\w+)( *)/'$1' /,-1,&allv_y));
%put &=allv_y3;

For your data step syntax: Just assign the max length of 32767. It's only one iteration of a data _null_ step and though performance impact of a too long var will be minimal.