More on subset creation

poulravn

Hi, I am referring to the post https://communities.sas.com/message/166376#166376 from May this year.

The example explains how to create a new data table by 1) first adding af data filter, and 2) then subsetting:

dt = Open ( BigClass);

dt << data filter (add filter(columns(:age),where(:age=={12,13}), Mode (select(1)));

sub1 = dt<<subset(output table name("regions"),selected rows(1), selected columns (0));

As  result a data table, named "regions" is created. As expected.

I have created a similar example using my own data set (the BigClass example works!!), which does not work well for me. I suspect I have a misunderstand of something. Here is my program flow attached.

The difference between the BigClass example and my example is, that the data filter does not get selected, when I run the whole script. (I omitted the loading of the file from an Excel file to be able to send you the original code, which had certain names in it). To explain: There is a character column, from which I wnat to select only those rows where the text in the column starts with "d" or "I". I found it necessary to create a new column on which to perform the data filter. When I run the code in total, the rows do NOT get selected and the subsetted data table does not get created. If I first run the code UNTIL the creation of the new column, Onelettet, and THEN run the data filter, then the filter is created and the rows are selected. And finally the subset data table is created. But these things should happen either way how the script is run? OR?

This is fundamental to the use of scripting, so I need to get a deep understanding of these subtleties.

Regards

Poul

sample JSLtxt.txt.zip

ms

It is difficult to tell why it not works in a particular case without a table for testing the code. If code depends on column formulas created in the same script, I have experienced that formula evaluation can lag behind the script execution which can lead to unexpected results. To avoid this one can try to insert Wait() or << eval formula at the critical steps.

However in this particular case, if I understand it correctly, there is no need for a new column (or a data filter). Perhaps the below example can be of help.

//example table

dt = New Table( "test",

  Add Rows( 5 ),

  New Column( "Instr", Character,

  Set Values( {"drum", "trumpet", "lyre", "clarinet", "didgeridoo"} )

  )

);

// Select instr starting with d or l using a data filter

sel = dt << get rows where( Starts With( :instr, "d" ) | Starts With( :instr, "l" ) );

dt << data filter(

  add filter( columns( :instr ), where( :instr = :instr[sel] ), mode( select( 1 ) ) )

);

dt << subset( output table name( "sel instr" ), Selected Rows( 1 ), selected columns( 0 ) );

// Or a more direct approach without data filter

dt << select where( Starts With( :instr, "d" ) | Starts With( :instr, "l" ) );

dt << subset( output table name( "sel instr" ), Selected Rows( 1 ), selected columns( 0 ) );