My guess would be that is has nothing to do with the file path or your way of exporting, but is what it says - a process is still holding the file,   Does it also happen if you close your EG session and start another? That should close your workspace-server session, but it happens now and then (at least to us) that an orphaned process is left running and holds a file forever, and then you need a system programmer to identify it and kill it. We recently had a situation where we had to reboot the cluster to get a file freed. ... View more

Hi @dxs8292    I wonder why you need all the looping. If I understand your code, you create several lists of variables, where each list contains variables with counter value in a given 1000-interval, and then you drop all these variables from the output data set.    As pointed out by others, there are several things that can give problems, and with a simpler code fewer things can go wrong. So - unless I missed something - this code will do the same:   %macro extract(input_data, input_list, size, output_data); %let droplist =; proc sql noprint; select distinct trim(variable) into :numlist separated by ' ' from &input_list where counter <= %eval(&size*1000); quit; %if &sqlobs > 0 %then %let droplist = drop &numlist; data &output_data(compress=yes); set &input_data; &droplist; run; %mend;         ... View more

Hi @Ranjeeta    There is a difference between your logic and the description of what you want.   Your logic (with @novinosrin 's corrections)  says that HEM_QBP  and ISC_QBP should both be 0, but your description says that either  HEM_QBP or ISC_QBP should be 0.     ... View more

Hi @FreelanceReinh    Thank you.    I just came to think of another method to avoid the index function:   data _null_; var = 'c:\aa\bb\ff\tt\dir'; newvar = prxchange('s/(.*\\)(\w*\\\w*\\\w*$)/$2/',-1,trim(var)); put newvar; run; %let var = c:\aa\bb\ff\tt\dir; %let newvar = %sysfunc(prxchange(s/(.*\\)(\w*\\\w*\\\w*$)/$2/,-1,&var)); %put &newvar; ... View more

Hi @Tom    Thank you. I wasn't aware of the TRIMMED option. Actually, when I made the examples, it puzzled me that select into didn't trim by defaul. as I expected, when the option NOTRIM wasn't specified.       ... View more

Thang you - You are so right! - I have edited the post to make it better, but call scan seems to be the only secure method. There must be som way to do something similar in macro code or sql expressions, but I havn't found it yet. ... View more

Hi @sam1231    If you create a macro variable with a %let-statement, you never get leading or trailing blanks.   It only happens if you create the macro variable in a data step or proc sql. Note that you get leading blanks if the origin is a numeric variable and trailing blanks if it is a character variable.   Here is a bit of code you can try to explore the different ways of creating macro variables with or without blanks. Note the different behaviour in examples 3 and 6, where one would expect the same to happen.   /* Creation of macro variables with and without trailing blanks */ * Example 1 - Macro assignment - never with trailing blanks; %let var1 = first ; %let var2 = second ; %let var3 = 5 ; %put &var1*&var2*&var3*; /* From existing data - first make some test data with trailing blanks */ data test; length var1 var2 $10; var1 = 'first'; var2 = 'second'; var3 = 5; run; * Example 2 - Data step - symput - blanks preserved; data _null_; set test; call symput('var1',var1); call symput('var2',var2); call symput('var3',put(var3,8.)); run; %put &var1*&var2*&var3*; * Example 3 - Data step - symput of trimmed/stripped variables - without blanks; data _null_; set test; call symput('var1',trim(var1)); call symput('var2',trim(var2)); call symput('var3',strip(put(var3,8.))); run; %put &var1*&var2*&var3*; * Example 4 - Data step - symputx - without blanks; data _null_; set test; call symputx('var1',var1); call symputx('var2',var2); call symputx('var3',var3); run; %put &var1*&var2*&var3*; * Example 5 - Proc sql - select variables - blanks preserved; proc sql noprint; select var1, var2, var3 into :var1, :var2, :var3 from test; quit; %put &var1*&var2*&var3*; * Example 6 - Proc sql - Select trimmed variables - DOES NOT WORK - still with blanks; proc sql noprint; select trim(var1), trim(var2), strip(put(var3,8.)) into :var1, :var2, :var3 from test; quit; %put &var1*&var2*&var3*; /* Removing leading/trailing blanks from existing macro variables */ * Create test variables with blanks; data _null_; set test; call symput('var1',var1); call symput('var2',var2); call symput('var3',var3); run; %put &var1*&var2*&var3*; * Example 7 - Remove with macro assignment; %let var1 = &var1; %let var2 = &var2; %let var3 = &var3; %put &var1*&var2*&var3*; * Create test variables with blanks; data _null_; set test; call symput('var1',var1); call symput('var2',var2); call symput('var3',var3); run; %put &var1*&var2*&var3*; * Example 8 - Remove with %trim; %let var1 = %trim(&var1); %let var2 = %trim(&var2); %let var3 = %trim(&var3); %put &var1*&var2*&var3*; ... View more

Hi @sam1231    Scan from end of string to cope with a different number of path elements. Using -3 will give the third element seen from end of string.   If data step functions are used in macro code, as you do in your first answer, all functions must be inclosed in %sysfunc, not the whole expression, as shown in the second example.    Here are 4 different ways of getting the last 3 elements. Note that only the data step is "secure", the other 3 will give a wrong result if there are several occurences of the same directory name in the path.   Edited because @FreelanceReinh pointed out a major flaw in the code. it worked not on 'c:\amylase\yy\my\dc\output', because the index function found 'my' in 'amylase'. This is fixed so index is searching for '\my\'   /* Macro code */ %let var = c:\amylase\yy\my\dc\output; * with macro functions; %let newvar = %substr(&var,%index(&var,\%scan(&var,-3,\)\)+1); %put &=newvar; * with sysfunc + data step functions; %let newvar = %sysfunc(substr(&var,%sysfunc(index(&var,\%sysfunc(scan(&var,-3,\))\))+1)); %put &=newvar; /* From existing data */ data have; var = 'c:\amylase\yy\my\dc\output'; run; * Data step; data want; set have; call scan(var,-3,p,l,'\'); newwar = substr(var,p); run; * Proc sql; proc sql; create table want2 as select var, substr(var,index(var,'\'||scan(var,-3,'\')||'\')+1) as newvar from have; quit;         ... View more

Hi @Filipvdr    I have limited experience with SAS EG, but I can't se why not. Note that It requires your EG workspace server to be configured to allow the use of 'X' commands, and not all administrators like that.  ... View more

Hi @JJP1    Re your comment to my first point - I fail to see how the structure of your planned job could prevent the use of a transformation with user-written code. To DI Studio there is no difference between a standard transformation like an Extract and a transformation with User-Written code. it is just a "box" holding some sas code and connected via tables to other "boxes", so it can go between two joins, if you need to do something that is beyond the scope of the prepacked transformations.   An then to your problem. I can't see from the information given if the problem is insuffecient space in the work library or a problem with  other system ressources, but today memory is normally not a problem, if the SAS installation is configured to use it, so it might be a disk space problem. I suppose you have already run the cleanwork utility to free unused space in the work library.   I suggest a couple of experiments to see if the problem can be circumvented by reducing the amount of space needed in the work library. All transformations in a DI Studio job are running in the same process, so all work tables are retained until the process finishes. Try to split in two jobs, the first with the small table joins ending in a permanent table, and let the 4. join end with sorting on the "next" join key. Explanation follows.   Then start the next job with a sort just for testing purposes. Is it possible for you to sort the big dataset on the join key without running out of ressources - I mean just connect the table to a sort transformation, specify the key and let it write to the default work table? -    If it is not working, try to sort to a permanent table in a library defined with compress=yes, or compress=binary if you have long character variables. Be sure to register the output table from the sort transformation instead of using a loader, as the whole idea is to avoid work tables.    If that works, then you have no work tables left, but two permanent tables, both sorted on the merge key. You could try a join, but with both tables sorted, you have an extra option, because you can merge the two tables in a data step (requiring the dreaded User Written Code), and merge is very light on ressources, because it don't need any sort tables in the background,       ... View more