4

I am wondering if it is possible to use a Conditional Start transformation in SAS DIS to conditionally bypass another transformation based on whether a given table is empty (no rows). This link seems to indicate that the condition must be based on the value of a parameter variable.

One idea I had was to create a parameter value in a User Written Code transformation, and assign it different values depending on the state of the table, but this seems elaborate and inelegant, and I'm not sure that it would work. Is there a simpler way?

Much gratitude

Rookatu
  • 1,487
  • 3
  • 21
  • 50
  • Created a new SAS-DIS tag (unfortunately they're always lowercase, boo). I don't know DIS very well, so please feel free to propose a tag wiki for it. – Joe Jun 10 '15 at 17:27
  • (Do that by hovering over the tag, and clicking on 'edit'. It will go into a review queue after you've done that.) – Joe Jun 10 '15 at 17:33
  • Thanks Joe, this is much appreciated! I think it will get me out of the hair of many a SAS user who knows nothing of SAS DIS. May reduce the attention my posts get though :S. – Rookatu Jun 10 '15 at 20:54
  • Oh, please feel free to post to both tags at the same time as long as the question has anything at all to do with base SAS. It's just helpful to have the tag to make it easier to find DIS questions. – Joe Jun 11 '15 at 14:13

2 Answers2

3

To get this done without adding any user written transformation and perhaps elegantly you can try what i generally use. First, copy the numobs macro posted by @cmjohns ( https://stackoverflow.com/a/5664379/4653284 ) and add it to the precode of the jobflow. After that go to the starting transformation which needs to be skipped based on the observation and right click to get properties tab of that transformation. Then goto the "Precode and Postcode" tab and add the following code:

%macro dummy;
%if %nobs(&SYSLAST) gt 0 %then %do;

After that is done, goto the transformation which would be the last transformation to be skipped basis the observation count and right click to get properties of the transformation and goto Precode Postcode tab and add the following code:

%end;
%mend dummy;
%dummy;

If only 1 transformation needs to be skipped then you can add the post code and precode provided above in the same precode and post code tab of that single transformation to skip it based on observation count.

Note: SAS DI Studio 4.8 has Conditional Transformations which can solve the same problem. But for DI studio version before that we have to code to get the conditional exclusion of transformation(s).

Community
  • 1
  • 1
sushil
  • 1,576
  • 10
  • 14
  • Hey Sushil. Thanks for the answer. Just so I understand, this does not use the conditional start transformation at all, right? Also, when you say that the macro from @cmjohns should be added to the precode of the "jobflow", do you mean the precode of the transformation preceding the ones to be skipped? Finally, is there any way to achieve this using something like the conditional start transformation? The ideal solution would be something I could drag and drop into the grid rather than adding hard code to multiple transformations. Thanks! – Rookatu Jun 11 '15 at 15:33
  • yeah, you're right the above solution does not use the conditional transformation. That is available in DI studio 4.8 onwards. Regarding macro from @cmjohns, you would need to add that to the precode of the jobflow so that macro gets complied before the jobflow starts executing. For the last question,if you are on DI Studio version below 4.8 then you could create your own custom conditional start and stop transformation using the above provided logic and use it wherever needed. – sushil Jun 11 '15 at 16:15
  • Hey Sushil. I have SAS DIS 4.9, so the Conditional Start transformation is available to me (I mention it in my post). But whether I use this transformation or a custom one, your approach seems to require that the precode of *other* transformations be modified, and that is what I'm trying to avoid. I want to know how I can use the Conditional Start transformation (or custom) to achieve this. Thanks! – Rookatu Jun 11 '15 at 20:39
  • check out the user documentation, they have beautiful example presented there which uses conditional start and stop transformation: http://support.sas.com/documentation/cdl/en/etlug/67323/HTML/default/viewer.htm#n1bfkipzqtwuu4n1ceslphtyyz5q.htm . If you still have issue then let me know – sushil Jun 12 '15 at 02:11
  • Sushil, that is the same link I included in my original post. It does not really address what I am asking. How do I use that transformation to check if a table is empty and if so skip some transformations? – Rookatu Jun 12 '15 at 13:39
  • my bad..i missed that link. anyhow are you sure you are adding the following condition "%nobs(&SYSLAST) gt 0" (w/o quotes) in the condition tab in conditional start transformation. I think that should solve your issue. Try and get the conditional start and stop aligned the way it's done in the example and set the condition correctly. It should work – sushil Jun 15 '15 at 02:08
  • Hey Sushil, yes, I am sure. When the condition evaluates the details are "ETL_DIAG: Condition flow did not execute, condition was -1>0". So the number of observations evaluates to -1. I right clicked on the grid of the job, selected properties, and pasted @cmjohns code in the precode window of the Precode and Postcode tab. Was this the right place to do that? Thanks! – Rookatu Aug 05 '15 at 14:15
2

The original question asked how to tell if a table did not exist at all, and this answer solves that problem. It does not help tell if a dataset has zero rows.

Since your link says that it will simply %eval whatever you put in that box, you may be able to use the exist function.

For example:

%put %sysfunc(exist(sashelp.class));
%put %sysfunc(exist(sashelp.classzas));

The first returns 1 (true), the latter 0 (false) on my machine.

Joe
  • 62,789
  • 6
  • 49
  • 67
  • I don't have nor use DIS, so this is with the caveat that this is solely based on base SAS - please use with care. – Joe Jun 10 '15 at 17:27
  • Hey Joe. I realized after reading your post that I misrepresented what I want to do a bit. The table will always exist, but I want to proceed to the next transformation conditional on the table being *non-empty*. Sorry for this mixup! Any idea what code I might use there? I wasn't aware of the `exist` function. – Rookatu Jun 10 '15 at 20:55
  • 2
    I don't have DIS either but if it supports macros in the autocall library, you could use the `%nobs()` macro code from either @cmjohns or my own answer on this question: http://stackoverflow.com/questions/5658994/how-to-detect-how-many-observations-in-a-dataset-or-if-it-is-empty-in-sas – Robert Penridge Jun 11 '15 at 00:28