Good morning all,
as I'm not that expert in SAS programming (still waiting to be trained but, as usual, trying to be productive in the meantime) I'm having a problem to figure out how to launch/call a SAS EG process' "step #2" from a program macro I wrote into same process' step #1 (forgive me if I won't use the correct SAS terms too: still learning).
More details: my customer asked our team to retrieve bank transactions data, two years from "today" (date()) giving us customers codes. I'm not sure my approach is the best one but, as I'm not exactly an analyst but a programmer, I wrote the following:
process step 1:
- import the "requests" (Excel) file into a WORK.REQUESTS temporary table: multiple records with 4 fields, containing the customer's code we need
- import step leads to a "looping" program which reads such table, record by record (observations), extracts the fields (variables) and writes another temp table WORK.REQUEST (singular) so that next steps have variables available.
mainLoop program:
options timezone=cet;
/* Questa macro legge, in sequenza, tutti i record importati dagli step precedenti,
per ogni record crea, ex novo, una tabella temporanea (WORK.RICHIESTA) contenente
un solo record di 4 campi (esattamente uguale al formato della tabella importata).
Quindi lancia una query che, leggendo quel record, esegue tutti i successivi passi
della procedura, fino ad esportare i file richiesti.
(C)2022 - Intesa Sanpaolo - DC OP AML Inv and Sanction Filtering - Regole e Strumenti
*/
%macro loopOverTable();
%local recCount Contratto;
/* step #1: retrieve the inported table's RecordCount
in: WORK.EC24M_RICHIESTE
out: recCount (macro var)
*/
PROC SQL;
SELECT count(*)
into :recCount
from WORK.EC24M_RICHIESTE t1;
QUIT;
%let ix=1; /* init ix index */
/* debugging & performances */
%let tStart=%sysfunc(datetime()); /* whole time computing */
%let tStop=%sysfunc(datetime()); /* single step time computing */
/* DEBUG: redefine recCount to avoid looping the whole table (while developing) */
/*%let recCount=3;*/
/* DEBUG: redefine recCount to avoid looping the whole table */
%if(&recCount > 0) %then /* no requests for the day -> recCount = zero */
%do %while (&ix <= &recCount);
data _NULL_;
/* step #2: create a single record WORK.EC24M_RICHIESTA table, with all 4 fields
in: ix (record index)
out: Filiale, Categoria, Conto, contratto (1 record from WORK.EC24M_RICHIESTE, pointed by ix)
*/
set WORK.EC24M_RICHIESTE (firstobs=&ix. obs=&ix.);
call symput("Contratto", strip(contratto)); /* retrieved variable will be NUMERIC */
proc sql;
create table WORK.EC24M_RICHIESTA as
select t1.Filiale,
t1.Categoria,
t1.Conto,
t1.contratto
from WORK.EC24M_RICHIESTE t1
where t1.contratto = "&Contratto."; /* "&Contratto." typecasts NUMERIC -> STRING */
quit;
run; /* data */
%put Contratto(&ix): &Contratto;
/* At this point we have a (new) table, WORK.RICHIESTA with a single record, 4 fields:
Filiale
Categoria
Conto
contratto
Now we should launch Procedure's step 02 (which, in turn, will launch the next steps too)
*/
/*
/-- LAUNCH STEP 02 HERE --\
*/
/* ........................... */
/*
\-- LAUNCH STEP 02 HERE --/
*/
/* Debug & Performances: show current ix */
%PUT Current index = &ix; /* leave a track in the log */
%let ix=%eval(&ix.+1); /* next record: ix++ */
/* Debug & Performances: calculate single cycle dT */
%let deltaT=%sysevalf(%sysfunc(datetime())-&tStop);
%put Step run time = &deltaT sec;
%let deltaT=%sysevalf(%sysfunc(datetime())-&tStart);
%put Cumulative run time = &deltaT sec;
%let tStop=%sysfunc(datetime()); /* update time ref*/
%put ;
%end; /* do loop */
%else; /* if recCount == 0 do nothing (for now) */
/* Debug & Performances: calculate total dT */
%let deltaT=%sysevalf(%sysfunc(datetime())-&tStart);
%put Total run time = &deltaT sec;
%mend;
/* Loops over imported file's records, launching the next steps giving them access to record's fields by means
of a temp table (1 rec).
in: WORK.EC24M_RICHIESTE 5 fields, N records
out: WORK.RICHIESTA 4 fields, 1 record
*/
%loopOverTable()(mainLoop code)
process step 2:
- my team is designing SAS EG process which reads the single observation from table WORK.REQUEST and, basing on a variable ("contratto"), extracts the yearly data for "current" and "previous" years (if available)
- next sub/steps triggered by step2a (...etc. etc...): it will end by exporting a number (5 or 6) of Excel files per customer code, depending on transactions data have been found.
Step1 works already, and I'm having the single observation work table with variables.
The problem is: how, in step1b macro mainLoop can I "call" the whole step2 execution, like one can do by hand from SAS EG project? (or a sub-step, still in step2, or, if "step boundary" is a problem, even a sub-step 1c still into step1, which then triggers next ones)
That to avoid to select ALL the data, which can be huge amount, then sub-select the ones we need: by asking for the least data since beginning, the project's life could last longer, imho.
Thanks in advance to whoever will be able to give an hint for our needs, or give an alternative way to have the same results.
Still trying to create a program/macro to loop over the imported table record, then submitting the main SAS EG (point&click) process by passing a single "input" (a code) to it, so to work with minimal data.
As it seemed not viable to "call a SAS EG (graphic) item" via macro, a friend suggested me to split my macro in two:
1. mainLoop: the looping one, which reads a (now 3 obs) imported table (WORK.EC24M_RICHIESTE) and write a single obs one (WORK.EC24M_RICHIESTA), time by time
2. singleRec: a new macro, which read the single obs table written by the looping one (WORK.EC24M_RICHIESTA) and does nothing (I made it writing another single obs table (WORK.EC24M_RICHIESTA_SHORT), just for testing/debug purposes)
Thus I got to this situation:
as you can see, there is no "link" from mainLoop macro to Estrazione_movimenti querybuilder, which then leads to the remaining project's steps: I linked singleRec to it, instead, expecting it, when engaged by mainLoop
(...)
/* At this point we have a (new) table, WORK.RICHIESTA with a single record, 4 fields:
Filiale, Categoria, Conto, contratto
Now we should launch Procedure's step 02 (which, in turn, will launch the next steps too)
*/
data _NULL_;
call execute ('%singleRec()');
run;
(...)to run the querybuilder too.
My thoughts was: mainLoop can't be linked to the querybuilder, 'cause if I do, it will loop N times then engages it (so the querybuilder will have the last record only, to work on) but, if I call N times singleRec, which is linked to the querybuilder, then the query will receive a different record (from the chain mainLoop -> singleRec) every time, which was my initial target (so that my colleagues can keep working point&click and I can keep looking at formulas, programs and macros).
...but...
SAS seems not liking my way of thinking (it's probably still me I don't know SAS way of thinking): mainLoop executes and loops 3 times (as the input table is of 3 obs), it engages singleRec all the 3 times, after writing the single obs table, but the querybuilder (Estrazione_movimenti), linked to singleRec, never start.
During the last week I read a lot of docs, together with many "solutions", here, in StackOverflow, everywhere.
I tried such "link" by using %include %singleRec(); which worked but, being singleReg there, doing nothing at all but just being linked to the querybuilder, this way did nothing too.
I tried using rc = dosubl('%singleRec()'); which brought me to a different problem (you know "every single solution will give two problems" ;-)), receiving a buffer length error of some kind (I didn't identified the effective problem source, to be honest).
My last try was the abovementioned call execute, which works, but not "as expected": is there a way, if I link a program to a querybuilder, to make the querybuilder run, after the program ends?
P.S.
I had doubts that call execute worked, as I read it lead to the execution after the calling element (mainLoop) ended, that's why I tried using dosubl, which I read it executes immediately. But, all apart, it seems call execute launches singleRec at every loop step (which is what I wanted), so I probably misunderstood the docs.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
1702 views
-
5 likes
-
-
