how to use a group of columns in a transpose statement in JSL?

mikeret57

Greetings, 

     Long time SAS programmer but new to JSL.   I have a data table and have been able to create a group containing say 20 columns.  Running this report next week, It may have 25 columns in the same group and I am looking to find a way to transpose the data table using this variable list of columns.  Using JSL, how does one modify the script below to operate off the group name instead of the individual list of columns?  The column name is "HB_001 etc."

Data Table( "tran_HB" ) << Transpose(

  columns(

  :HB_001,

  :HB_002,

  :HB_003,

  :HB_004,

  :HB_005,

  :HB_006,

  :HB_007,

  :HB_008,

  :HB_010,

  :HB_012

  ),

  By( :Family, :group ),

  Label( :Label ),

  Output Table( "sum_HB_w_0" )

);

Thanks,  Mike

brady_brady

****EDIT:

I did not realize that the columns were in a physical column group (I was thinking of a logical group.)

This appears to have been answered in another post (also Mikeret57's) for this same question. If the columns are in a physical group you can (and by all means should) forgo the tedious approaches below.

*** End EDIT

Here is one way to do it (there are many others) that is hopefully relatively understandable.

Basically you build an expression that contains the columns you want (here, any column with name starting with HB_), then substitute this expression (in place of a placeholder that you've used for this purpose... in this case I called the placeholder "cols") into an expression housing the transpose statement, and evaluate this newly formed expression.

dt = Data Table( "tran_HB" );

collist = (dt << getcolumnnames);

colexpr = Expr( Columns() );

For( i = 1, i <= N Items( collist ), i++,

    If( Left( Char( collist ), 3 ) == "HB_",

        Insert Into( colexpr, collist )

    )

);

trans_expr = Expr(

    dt << Transpose(

        cols,

        By( :Family, :group ),

        Label( :Label ),

        Output Table( "sum_HB_w_0" )

    )

);

Eval( Substitute( trans_expr, Expr( cols ), Expr( collist() ) ) );

Here is another way that uses string operations:

dt = Data Table( "tran_HB" );

collist = (dt << getcolumnnames(string));

for (i=nitems(collist), i>=1, i--,

    if(left(collist,3)!="HB_",removefrom(collist,i))

);

eval(parse(substitute(

    "\[

        dt << Transpose(

            columns(:xxx),

            By( :Family, :group ),

            Label( :Label ),

            Output Table( "sum_HB_w_0" )

        )

    ]\",

    "xxx", concatitems(collist, ", :")

)));

Cheers,

Brady