Patrick Tracker

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.

Re: sas add single quotes for each argument in macro variable

Patrick — Sun, 07 Dec 2025 11:02:00 GMT

Here one way to go

%let My1=X Y Z;
%let My2=%sysfunc(prxchange(s/(\w+)/'$1'/i,-1,&My1));
%put &=My2;

 69         %let My1=X Y Z;
 70         %let My2=%sysfunc(prxchange(s/(\w+)/'$1'/i,-1,&My1));
 71         %put &=My2;
 MY2='X' 'Y' 'Z'

Re: Prevent warning whgen var not exist

Patrick — Sun, 07 Dec 2025 04:50:21 GMT

@Ronein wrote:
Let's say i want to prevent warning only on specific code and when continue other codes warning will behave normally

@Tom already shared how this can be done.

Another option is to build your list of vars to keep dynamically to only contain variable names that actually exist in your source table.

proc sql noprint;
  select name into :keeplist separated by ' '
  from dictionary.columns
  where libname='SASHELP' and memname='CARS'
    and upcase(name) in ('MODEL','MAKE','INVOICE','COLOR')
  ;
quit;
 
data cars;
  set sashelp.cars(KEEP=&keeplist);
run;

Re: Extract Number From String and Convert to Number

Patrick — Thu, 04 Dec 2025 22:43:00 GMT

Something like below should do.

data have;
  input var1 $ var2 $;
datalines;
ROOMA 	DESK01A 	
ROOMA 	DESK01B 	
ROOMA 	DESK05B 	
ROOMA 	DESK06A 	
ROOMB 	DESK09A 	
ROOMB 	DESK09B 	
ROOMC 	DESK01B 	
ROOMC 	DESK05A 	
ROOMD 	DESK10B 	
ROOME 	DESK15A 	
ROOME 	DESK15B 	
ROOME 	DESK16 	
ROOME 	DESK 	
;

proc format;
	value range
	.   			= 'missing'
	low - 5 	= 'Range 1'
	5 -  10 	= 'Range 2'
	10 - high	= 'Range 3'
	;
run;

data want;
	set have;
	nums=input(scan(var2,1,,'kd'),best32.);
run;

proc sql;
	select var1, var2, nums as nums1 format=best32., nums as nums2 format=range.
	from want
	;
quit;

Re: Installing a Postgresql instance inside a SAS environment (Windows Installation)

Patrick — Mon, 01 Dec 2025 22:27:30 GMT

One indication that installing another instance shouldn't create any issues:
SAS Risk Solutions also use Postgres and it's not the WIP server but a separate instance (with likely separate binaries).

Re: Newbie data processing question - frequency across two columns?

Patrick — Tue, 18 Nov 2025 08:39:12 GMT

In my opinion CAS is not really the place for data prep and though if it was me, I'd use Compute to read the Excel files and prepare a table that's suitable for VA. It's then this table that I'd load into CAS for VA reporting.

You can create such a process using SAS Studio flow and then schedule the flow.

You also need to look up/skill up how to define your CAS based reporting data so the VA report autoloads the data into CAS after server restart (which will wipe-out all CAS memory based tables).

Did you already try to adopt any of the proposed solutions to your environment? If not then that's what you eventually should be doing next so you can ask targeted follow-up questions if you get stuck.

Re: Newbie data processing question - frequency across two columns?

Patrick — Sat, 15 Nov 2025 00:48:52 GMT

Are your Have tables already loaded into CAS or are these just normal SAS files and data prep could also happen under Compute?
What volumes are you dealing with?

Is your WANT a table or a report?

For creating a table something like below could work. I can't test it but I believe if all your tables are in CAS then the whole process will execute within CAS.

data writer;
  infile datalines truncover dsd dlm='|';
  input writer:$40.;
  datalines;
Joe
Mac
Maggie
Sam
;

data lead_assistant_project;
  infile datalines truncover dsd dlm='|';
  input Lead_Writer:$40. Assistant_Writer:$40. Project:$40.;
  datalines;
Joe|Mac,Maggie|Earwax for Dummies
Joe||Clock Repair
Sam|Joe|Desk Fans: A Celebration
;

/* restructure your data into a form that's easier to work with */
data writer_role_project;
  length writer $40 role $10;
  set lead_assistant_project;
  keep writer role project;
  
  role='Lead';
  do i=0 to countc(Lead_Writer,',');
    writer=scan(Lead_Writer,i+1,',');
    if not missing(writer) then output;
  end;
 
  role='Assistant';
  do i=0 to countc(Assistant_Writer,',');
    writer=scan(Assistant_Writer,i+1,',');
    if not missing(writer) then output;
  end;
 
run;

proc fedsql;
/*   create table want as */
  select 
    coalesce(t1.writer,t2.writer) as writer
    ,sum(case when t1.project='' then 0 else 1 end) as Total_Projects
    ,sum(case when t1.role='Lead' then 1 else 0 end) as Lead
    ,sum(case when t1.role='Assistant' then 1 else 0 end) as Assistant
  from writer_role_project t1
  full join writer t2 
    on t1.writer=t2.writer
  group by coalesce(t1.writer,t2.writer)
  ;
quit;

Re: Subject: ERROR: File not in correct format for engine (X_CUSTOMISED_CODE.ECL_DATA_PART.DATA) in

Patrick — Fri, 14 Nov 2025 07:47:18 GMT

Suggest you move your question under Solutions/Risk and Fraud/SAS Risk Management

From what I understand and based on the error message, I believe you are on the right track. My first question would be: Has it ever worked before? What changed since?

If you can amend the code and add %include ...../source2; and also try to run in debug mode and with options mprint

I the Viya version is now called ACL and that IRM has been replaced so you must be running a solution version under SAS 9.4. CAS is Viya only.

It's too long ago that I actually did hands-on IFRS9 work and it's certainly a specialty skill. Eventually ask SAS Tech Support directly because I don't expect too many people here in the forums to know the solution in the depth needed to support you.