It would be slightly faster to do it this way: data tran_data;     set tran_data (drop=unused_trans); run; ... View more

Looks like you didn't need %str before, but you will need it now because of the equal sign and the added parentheses. Yes, you need the parentheses.  Without them, SAS would logically evaluate AND first (before OR).  With them, you can force SAS to evaluate OR first which is what your example requires. When referring to parentheses inside %STR, you need to add an extra percent sign: variable = %str( %(aetdate=. or aetdate > factdat%) & trtrel) Good luck. ... View more

If all else fails ... You know the location of the SAS WORK folders. Linux should be able to count how many folders belong to the current user. There are pitfalls, like cancelled sessions that never erased the WORK area.  But at least it's a possible approach. Good luck. ... View more

ZRick, I can't help but wonder if we're approaching this problem the right way.  There's an earlier step that generates all these lines of SAS code.  Now we're looking to reorganize them and run them.  Would it make sense to break into the earlier step instead?  Right now it generates several streams of SAS statements.  Would it make more sense for the earlier step to generate stream1.sas with all the cat=1 lines, stream2.sas with all the cat=2 lines, etc.?  You would have ready-to-run programs holding all the proper statements. Just a thought. ... View more

Well, if you have a macro variable holding all the statements that should execute, just add: &macrovar; I added the semicolon at the end to allow for my variation where the final semicolon had to be added. You might play with these options first to find a combination that would give you just one version of the statements on the log: options nosymbolgen; options nomprint; And if you don't need to "save up" the statements but can run them immediately, you could skip creating a macro variable entirely.  Here's a variation on your original DATA step: data _null_; length stmt $ 50; input stmt $ &; call execute (stmt || ';'); cards; ... This approach has a theoretical drawback.  If the incoming lines contain any macro language commands (rather than DATA step statements), they will execute immediately (out of order) before the DATA step statements execute. Good luck. ... View more

Here's a neat way to combine them.  You still might face the issues you saw when printing with %PUT (although it's not clear why in the world you would want to use %PUT to output the results): proc sql;    select trim(stmt) into : macrovar separated by ';' from t; quit; You would still need to add one final semicolon at the end. Good luck. ... View more

Should also note the definition of the "last record in the block". Given the PRODNO and CPNP, the last record in the block is the one with the largest value of PLANT, and within the largest value of PLANT, the largest value of PART_NO, and within the largest value of PART_NO, the largest value of COST_YEAR. I still think the original code is wrong, but at least you know what it means. Good luck. ... View more

OK, here's what it says to do.  The key statements: BY PRODNO CPNP PLANT PART_NO COST_YEAR; IF NOT FIRST.CPNP and LAST.CPNP and PLANT='USM' THEN DELETE; Assuming that these are the statements in the original code, it means: If a block of records (must be more than one record in the block): share the same PRODNO and CPNP, and the last record in the block is USM, then delete that last record of the block, keeping all the earlier records in the block. You can see why many of us doubt that this is the right thing to do, but that's what the code is saying. Good luck. ... View more

Even if DM.TABLE were a SAS data set, the structure might have changed between runs.  Here is a simple change: data DM.TABLE; set DM.TABLE; run; This step could cause SQL to retrieve records in a different order.  How?  Well, it is possible that the original data set had an index where COMPANY was the primary indexed variable.  Running through the DATA step destroys the reliability of the index.  So the first SQL uses the index and the second one doesn't.  We need to know more about where the data comes from, and what changed in between the first and second SQL runs. ... View more

In a way, the question becomes how much responsibility do you want to undertake. Do you want to convert the code to SQL, even if the code is wrong? Or do you want to understand what records are supposed to be deleted, and make the SQL version produce the correct result? Either way, you can get answers here.  But you might have to make your choice first. It sure looks like the DELETE statement that you are working with is incorrect.  It would operate as if there were parentheses in place: IF (NOT FIRST.CPNP) and (LAST.CPNP) and (PLANT='USM') THEN DELETE; We can tell you what the code does, but we can only guess at what the code is supposed to do. ... View more

This would happen if the source data (DM.TABLE) changed in between the two runs.  It could be as simple as having run a PROC SORT in between. ... View more