@Martin_Bryant wrote: Many thanks. All of our files are stored on the server and we only get relative names, so I'm not sure that this will work for us, but it looks very useful if I can work out how to access our paths. Many thanks. We aren't allowed to store files externally to the server. Sigh. What do you mean by relative names? Relative paths beginning from what's defined for EG folder "Files? If so right click onto one of the programs in EG and select "Copy path" and then paste it into a program node. That should give you the absolute path <path to Files>/<relative path>   With SAS 9.4 DI Studio got a loop transformation that also allows for parallel execution of the loops.  With Viya SAS Studio Flow there isn't a loop step yet but it shouldn't be hard to create a custom step for this (2 steps actually, loop start and loop end). There is a migration path for SAS EG flows to SAS Studio Flow.   ... View more

It's possible that your variable sex got a format attached to it and that you're trying to use the formatted values. Please run below code and share with us the result of the two procedures. data test; set <your table>; sex2=sex; run; proc contents data=test(keep=sex); run; proc freq data=test; table sex2 /missing nocol norow nopercent; run;   ... View more

The number of SQLs you send needs to be relatively small for this to potentially outperform the temp table approach. It should be o.k. for 10K keys. If it's for character then just also add single quotes around the values. keylist=catx(',',keylist,cats("'",key,"'")); ... View more

@SAS_in_Florida wrote: The data transfer into Teradata completes without error. The timeout happens during the inner join. I will look into finding an appropriate Teradata admin to get their input. Thank you for the suggestion. @SAS_in_Florida  It's certainly a good idea to have a Teradata DBA look into the reason of why the inner join takes so long.    One strategy for improving performance that worked for me in the past for such a heterogenous inner join situation was to generate SQL with a where clause instead of an inner join. For many databases the limit for a single SQL is 32kb so you need to generate and send multiple queries.   Here some sample code to illustrate the approach. data tera_big; val='1'; do key=1 to 1000000; do j=1 to 100; output; end; end; run; data sas_small; do key=1 to 1000000 by 100; output; end; run; %macro sql_pump(keylist); proc sql; create table work.result_set as select key,val from tera_big where key in (%unquote(&keylist)) ; quit; proc datasets lib=work nolist; append data=work.result_set base=work.want; run; delete result_set; run; quit; %mend; data _null_; set sas_small end=last; by key; if last.key; length keylist $32000; retain keylist; keylist=catx(',',keylist,put(key,f16. -l)); if _n_=1 then do; call execute('proc datasets lib=work nolist nowarn; delete want; quit;'); end; if last or length(keylist)>31000 then do; call execute( cats('%sql_pump(%nrstr(',keylist,'));') ); call missing(keylist); output; end; run;   ... View more

@aalborz-ema This is hopefully a super simple environment that doesn't require any metadata security. How many users do you have? I'm also a bit astonished that for a brand new installation this appears to be SAS 9.4 and not SAS Viya. To be a SAS admin you MUST get training. If it's a very simple installation then you might get away with a few days only. If it's a higher complexity environment but your organisation can't spend the money on a trained SAS admin then I'm a bit surprised that "you" didn't opt for a SAS managed (hosted) environment where SAS would do this job for you.   "My specific issue is that in the SAS Management Console I don't see anything." You did define a connection to the SAS Metadata server (server name, port, credentials) - right? ... View more

@Rou wrote: I am preparing a report, so I need it in a wide format.  Most SAS Procs like Proc Report and Proc Tabulate work much better with long data. It's then the report procedure that allows you to create wide cross-tab reports. ... View more

It was at least for me not clear from your description that you want to delete ALL the highlighted rows. Below should do this. data have; infile datalines dlm=',' dsd truncover; input NO $ Code_2 $ DATE:ddmmyy10. CD$; format date date9.; some_other_var=_n_; datalines; B17,214,18/07/2019,TIN B17,214,6/08/2019,TIN B17,214,6/08/2019,XTIN B17,214,10/12/2019,TINK B17,214,10/12/2019,XTINK MK2,300,18/10/2019,TIN MK2,300,19/10/2019,MIN MK2,300,19/10/2019,XMIN MK2,300,21/10/2019,PAP MK2,300,22/10/2019,PAK ; proc sql; /* create table want_1 as*/ select l.* from have l left join (select no,code_2,date,substr(cd,2) as _cd from have where upcase(substr(cd,1,1))='X') r on l.no=r.no and l.code_2=r.code_2 and l.date=r.date where missing(r._cd) ; quit; ... View more

To just delete any cancelled event a where clause would do. If you want to keep cancelled events without a matching event on the same date then code as below should work. The last row in below data contains the case where there is a cancelled event (XTINK) without a matching event. data have; infile datalines dlm=',' dsd truncover; input NO $ Code_2 $ DATE:ddmmyy10. CD$; format date date9.; some_other_var=_n_; datalines; B17,214,18/07/2019,TIN B17,214,6/08/2019,TIN B17,214,6/08/2019,XTIN B17,214,10/12/2019,TINK B17,214,10/12/2019,XTINK MK2,300,18/10/2019,TIN MK2,300,19/10/2019,MIN MK2,300,19/10/2019,XMIN MK2,300,21/10/2019,PAP MK2,300,22/10/2019,PAK MK2,300,01/12/2019,XTINK ; /* option 1 */ proc sql; create table want_1 as select *, count(*) as cnt from have group by no,code_2,date having cnt=1 or cnt>1 and upcase(substr(cd,1,1)) ne 'X' ; quit; /* option 2 */ proc sort data=have out=inter; by no Code_2 date; run; data want_2; set inter; by no Code_2 date; if not first.date and upcase(substr(cd,1,1)) = 'X' then delete; run;   ... View more

@SAS_in_Florida wrote: Never used forums before and this may be pretty easy but I am  struggling with this at the moment. Any suggestions are appreciated.   Currently Using SAS Studio but might be moving to SAS VIYA in future.   I have an inventory of ~100k records that I am joining/merging with another table. The time it takes to run the query is exceeding the allowed amount of time to run against the data tables and the program is being aborted as a result. My goal, is to split my inventory into smaller inventory files of ~10K records and run the query multiple times and set the results back together upon completion. If the record count was the same each time, I could just hard code it. However, the inventory record count could vary daily.   @SAS_in_Florida  SAS Studio won't go away. It is the main client with SAS Viya.   Soo... You've got some big table in Teradata and you've got a relatively small table (100k rows) that you need to join. Is the small table in SAS or in Teradata? How big is the Teradata table (number of rows?). You need to join the big with the small table (inner join) and then get the result set back into SAS for further processing What's the relationship between the tables? Would the result set be 100k rows or could it be much more? Ensure that the join key (single or composite) in your small table is unique. How do you know that it's the query in Teradata that takes too long and not just the data transfer between Teradata and SAS?   The usual approach for a performant join between a "small" SAS table with a big DB table is to first upload the join keys of the small table into a Teradata table (temp table or permanent table), execute the join in Teradata using explicit passthrough SQL and then transfer the resultset back to SAS. Are you allowed to create a table in Teradata (permanent or temporary)?   What you also need to "optimize" is in what junks SAS exchanges data with Teradata because the defaults are often too low. Verify that for your libname/connection to Teradata the following options have "reasonable" values: readbuff=<some value>, insertbuff=<some value>, dbcommit=0   If you don't use explicit passthrough SQL then also use the following options to ensure that processing gets fully pushed to the DB: options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i ... View more

@User_2024 Based on what you've told me below some "template" sample code to show how logic could work only using your one main table in Oracle. I would only switch to something more "complicated" for performance reasons - like maintaining a separate table with customer ids selected in earlier runs instead of working this out every single time like in below query. options dlcreatedir; libname ora "%sysfunc(pathname(work))/oracle"; libname ctrl "%sysfunc(pathname(work))/perm_storage"; /* master source table with all transactions */ data ora.master; infile datalines truncover dlm=','; input trans_id acct_id$ cust_id$ trans_type trans_dttm:anydtdtm.; format trans_dttm datetime20.; datalines; 1,a,x,1,01jan2024 2,a,x,2,01feb2024 3,b,x,1,01mar2024 2,a,x,2,02mar2024 2,b,x,2,02mar2024 ; /* control table to store max transaction date selected in a previous run */ /* - needs to be a permanent table - create table if it doesn't exist */ %if not %sysfunc(exist(ctrl.last_selected)) %then %do; data ctrl.last_selected; attrib run_dttm max_trans_dttm format=datetime20.; stop; run; %end; /* &prev_max_trans_dttm: max transaction date already selected in a previous run */ proc sql noprint; select coalesce(max(max_trans_dttm),0) format=datetime20. into :prev_max_trans_dttm trimmed from ctrl.last_selected ; quit; /* create data for reporting: - only include transactions of type 1 - only select transaction dates > &prev_max_trans_dttm - exclude transactions that already had been selected in earlier runs = transactions for customers that already had a type 1 transaction at or before &prev_max_trans_dttm */ proc sql; create table work.report_source as select cust_id, trans_type, trans_dttm from ( select cust_id, trans_type, trans_dttm from ora.master where trans_dttm>"&prev_max_trans_dttm"dt and trans_type=1 group by cust_id having max(trans_dttm)=trans_dttm ) where not exists ( select distinct cust_id from ora.master where trans_dttm<="&prev_max_trans_dttm"dt and trans_type=1 ) ; quit; /* add most current max transaction date selected to control table */ proc sql; insert into ctrl.last_selected set run_dttm=datetime(), max_trans_dttm=max("&prev_max_trans_dttm"dt,(select max(trans_dttm) from report_source)) ; quit; /* and here just some "template" to give you an idea how to deal with a zero row result */ data _null_; call symputx('nobs',nobs); stop; set work.report_source nobs=nobs; run; %put &=nobs; %if &nobs=0 %then %do; data _null_; file print; put "no rows selected"; run; %end; %else %do; proc print data=work.report_source; run; %end; ... View more

@User_2024 I felt I should share your reply to our private message here. I hope you don't mind.   Thank you for the suggestion, I will keep the discussions in public for all my posts from now on. But, let me answer for the questions here,  Is my understanding correct that in your master table you've got transactional data where in a single row you've got the customer_id, transaction type and transaction date? - Yes, your understanding is correct.    Let's say you're running your program on a Wednesday: Do I understand this right that the only customers you'd want to report on are the ones that had a type1 transaction on Tuesday (your most current data) and never had such a type 1 transaction on any earlier date? --- That would be correct, sometimes the payment table gets updated later in the day which "may" miss that days data if i run the job in the morning. That is why I always look back for last 5 weekdays.    If you run your program on a Monday then you would select the previous Friday-Sunday because the last report you run on Friday only included data up-to Thursday. - this is accurate.  And, I will run only on weekdays, but if we have a public holiday on a weekday, I think I have to think about how other similar jobs are set up among my team mates.  ... View more

@yabwon And for my own upskilling and because I could I've now also been running this aggregation step in Viya 4 CAS on an environment with 192 threads (4 CAS workers with 48 CPU's each). This would in reality only be worth it if the simulation process already runs in CAS and creates a CAS table.   The hash approach took 14 seconds. I've been told that if there is no hash method used that needs write access to the hash then CAS is smart enough to load the hash only once (not sure if once per worker or once at all on the controller node).   The temp array approach executed in CAS without errors or warning BUT it somehow skipped processing all the data. Not sure yet if this is a bug or if I'm just not understanding yet how things work. The moment I removed the do until step that loads the array the set statement read all the rows from casuser.table_a.   casuser.table_A is partitioned by I - which is why I don't need a 2nd single threaded pass through the data to consolidate the results from the different nodes ("MapReduce"). casuser.table_B is replicated on all nodes.   And here the log.   179 /************ hash lookup ************/ 180 data casuser.want_HASH(keep = i sum_x replace=yes); 181 if _n_ = 1 then 182 do; 183 if 0 then set casuser.table_A(keep=i id id_2 category) casuser.table_B(keep=x); 184 declare hash hh_bew (dataset: 'casuser.table_B'); 185 hh_bew.definekey ('id', 'id_2', 'category'); 186 hh_bew.definedata ('x'); 187 hh_bew.definedone (); 188 end; 189 190 set casuser.table_A(keep=i id id_2 category); 191 by i; 192 if hh_bew.find()=0 then sum_x + x; 193 if last.i then 194 do; 195 output; 196 call missing(sum_x); 197 end; 198 run; NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step will run in multiple threads. NOTE: There were 0 observations read from the table TABLE_A in caslib CASUSER(*****). NOTE: There were 0 observations read from the table TABLE_B in caslib CASUSER(*****). NOTE: There were 308519088 observations read from the table TABLE_A in caslib CASUSER(*****). NOTE: There were 4890072 observations read from the table table_B in caslib CASUSER(*****). NOTE: The table want_HASH in caslib CASUSER(*****) has 5552 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 13.56 seconds user cpu time 0.06 seconds system cpu time 0.02 seconds memory 1675.28k OS Memory 40812.00k Timestamp 03/08/2024 12:59:55 AM Step Count 18 Switch Count 2 Page Faults 0 Page Reclaims 140 Page Swaps 0 Voluntary Context Switches 275 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 199 200 /************ temporary array direct key lookup ************/ 201 %let max_i = 5600; 202 %let max_id = 55000; 203 %let max_category = 22; 204 %let max_ID_2=1; 205 206 data casuser.want_ARRAY(keep = I SUM_X replace=yes) ; 207 208 /* populate array */ 209 array XX[&max_id. /* 1 to 55000 */ 210 ,&max_category. /* 1 to 22 */ 211 ,0:1 /* for ID_2 values 0 to 1 */ 212 ] _temporary_; 213 if _n_=1 then 214 do until(eofB); 215 set casuser.table_B end=EOFB; 216 XX[ID,CATEGORY,input(ID_2,1.)]=X; 217 end; 218 219 set casuser.table_A; 220 by I; 221 222 if first.I then SUM_X = 0; 223 SUM_X + XX[ID,CATEGORY,input(ID_2,1.)]; 224 225 if last.I then output; 226 227 run; NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step will run in multiple threads. NOTE: There were 1222518 observations read from the table TABLE_B in caslib CASUSER(*****). NOTE: There were 76796502 observations read from the table TABLE_A in caslib CASUSER(*****). NOTE: The table want_ARRAY in caslib CASUSER(*****) has 1382 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 12.74 seconds user cpu time 0.03 seconds system cpu time 0.03 seconds memory 20470.84k OS Memory 59720.00k Timestamp 03/08/2024 01:00:07 AM Step Count 19 Switch Count 2 Page Faults 0 Page Reclaims 703 Page Swaps 0 Voluntary Context Switches 238 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0         ... View more