How to get rows into a data step

[sathya66]

Hi All,

I have  a table which contains  values of "if then else conditions" in a variable. how can loop them in a data step.

currently we are copying all rows from that column into a data step like below

data have;
SET test
(WHERE=(TIMESTAMP  BETWEEN &START. AND &END.));

/* row1 */  IF ( domain GE 21.00000 ) THEN name = '03. >= 21.00000';         
/* row2 */  IF ( SYSTEM EQ -999999 ) THEN op = '01. Especial Value = -999999'; ELSE 
/* row3 */  IF ( SYSTEM LT 17.00000 ) THEN op = '02. < 17.00000'; ELSE 
/* row4 */  IF ( SYSTEM GE 17.00000 AND SYSTEM LT 20.00000 ) THEN op = '03. >= 17.00000 and < 20.00000'; ELSE 
/* row5 */  IF ( SYSTEM GE 20.00000 ) THEN op = '04. >= 20.00000';
/* row6 */  IF ( region LT 2.00000 ) THEN LN = '01. < 2.00000'; ELSE 
/* row7 */  IF ( region GE 2.00000 AND region LT 10.00000 ) THEN LN = '02. >= 2.00000 and < 10.00000'; ELSE 
/* row8 */  IF ( region GE 10.00000 AND region LT 11.00000 ) THEN LN = '03. >= 10.00000 and < 11.00000'; ELSE 
/* row9 */  IF ( region GE 11.00000 ) THEN LN = '04. >= 11.00000';         
/* row10 */  IF ( status LT 2.00000 ) THEN cn = '01. < 2.00000'; ELSE 
/* row11 */  IF ( status GE 2.00000 AND status LT 4.00000 ) THEN cn = '02. >= 2.00000 and < 4.00000'; ELSE 
/* row12 */  IF ( status GE 4.00000 AND status LT 7.00000 ) THEN cn = '03. >= 4.00000 and < 7.00000'; ELSE 
/* row13 */  IF ( status GE 7.00000 ) THEN cn = '04. >= 7.00000';


run;

I am looking like below macro that loops all rows.

data have;
SET test
(WHERE=(TIMESTAMP  BETWEEN &START. AND &END.));

<&rows>;


run;

Thanks,

Sathya.

[PaigeMiller]

I believe I understand what you are trying to do (although I am not 100% sure I do understand properly).

You have a data set with text strings that are actually SAS IF-THEN-ELSE commands. I'm going to call this data set DATASET_WITH_IF_THEN_ELSE;

Furthermore, in this data set, you have a text string which is in the variable named COMMAND.

If this really matches your situation (although possibly with different data set name and variable name), then this will work for you:

/* UNTESTED CODE */
data _null_;
    set dataset_with_if_then_else end=eof;
    call execute('data have;
        set test(where=(timestamp between &start end &end));
        ');
    call execute(command);
    if eof then call execute('run;');
run;

If the commands are not in a SAS data set but just a plain text file, which I have creatively named text_file.txt, then this should work:

/* UNTESTED CODE */
data have;
    set test(where=(timestamp between &start end &end));
    %include "text_file.txt";
run;

If the above description of the problem is off target somehow, please explain further.

[sathya66]

Thanks, You understood it correctly. I have SAS IF-THEN-ELSE commands in a variable(ie : command variable)  in test table only not in a different table.

so my code will be like below?

/* UNTESTED CODE */
data _null_;
    set test end=eof;
    call execute('data have;
        set test(where=(timestamp between &start end &end));
        ');
    call execute(command);
    if eof then call execute('run;');
run;

[Tom · Accepted solution]

Forget CALL EXECUTE().  Just treat the data as data.  Run a data step to write the code into an actual code file. 

filename code temp;
data _null_;
  set test;
  file code;
  put command;
run;

Then use %INCLUDE to run the code wherever you need it.

data WANT;
   set test (drop=command);
   where (timestamp between &start and &end) ;
%include code / source2 ;
run;

It is not at all clear to me if the WHERE belongs on the first step or the second (or both).

[ballardw]

Quite often these sorts of If/then/else when using a single value to create or assign a new value to a different variable can be done with custom formats. Which moves such logic out of the data step.

If the repeated use of your new values starting with 01. 02. 03. etc is there for sort order purposes then a format, since it applies to the underlying value, would display in proper numeric value order (unless used in procedures that explicitly allow changing order to use formatted values).

Then running this code is not even needed.

Example of one format:

Proc format;
value op
-999999 =   '01. Especial Value = -999999'
/* the -999999 below would likely better be the smallest 
   value you expect for the variable
*/
-999999 < - 17.00000 =  '02. < 17.00000'
17.00000 - <20.00000 =  '03. >= 17.00000 and < 20.00000'
20.00000 - high = '04. >= 20.00000'
;
run;

One example of use:

Proc print data=yourdatasetwithSystem ;
   var system;
   format system op. ;
run;

Formats have a great deal of flexibility that is not easy with if/then/else code. First the same format can be applied to multiple variables with a single statement:

format thisvar thatvar anothervar varlist1-varlist25 customformatname. ;

another is that formats can be stored between sessions in permanent libraries to be available when needed.

The formats can also be placed into data sets for sharing with the Proc Format Cntlin and Cntlout options.

Another feature might address what appears to be 2 missing "rows" in your example. You start with

/* row1 */  IF ( domain GE 21.00000 ) THEN name = '03. >= 21.00000';    

But do not show, following the pattern for your other variables, what should be done with any DOMAIN with values less than 21, or "special" values.

The Proc format Value option "OTHER=" creates the value for any value not explicitly listed. Combined with the _error_ option it will also throw a log entry for any unexpected values encountered.

BTW, with this:

/* row6 */  IF ( region LT 2.00000 ) THEN LN = '01. < 2.00000'; ELSE 

when Region has a missing value do you actually want a result of '01. <2.0000' ? That is what the code will do.