topic Re: minimum values of column by group in cas data in Developers

minimum values of column by group in cas data

improvingCas — Tue, 31 Jul 2018 12:53:50 GMT

Hi !!
im new to sas viya

i don't know how to find min value of column by group

i have mycas.dist data like

id var1 var2 var3 distance
1 1 1 1 0.2
1 2 1 2 0.3
2 1 1 3 0.9
2 1 2 1 0.2
3 1 3 2 0.1

and i want to make table using mycas.dist like below table

id var1 var2 var3 distance
1 1 1 1 0.2
2 1 2 1 0.2
3 1 3 2 0.1

can i do it using cas data?

Re: minimum values of column by group in cas data

MikeMcKiernan — Fri, 03 Aug 2018 12:19:47 GMT

There is a DATA step solution. (Hmm...maybe there's a DATA step solution to everything.)

Since this requires BY-group processing, start by loading the data into partitions:

libname mycas cas;

data mycas.have(partition=(id) orderby=(distance) replace=yes);
  input id var1 var2 var3 distance;
  datalines;
1 1 1 1 0.2
1 2 1 2 0.3
2 1 1 3 0.9
2 1 2 1 0.2
3 1 3 2 0.1
;
run;

Then, use the SESSREF= DATA statement argument to insist that the DATA step runs in CAS:

data mycas.want / sessref=casauto;
   set mycas.have;
   by id distance;
   if first.id then output;
run;

Finally, check the work:

proc print data=mycas.want;
run;

There's lots of excellent DATA step in CAS documentation, such as the following:

BY-group processing in CAS

(I made an update to this post because I left off the ORDERBY= data set option on the first DATA step. With the option, the table is already organized according to the BY statement in the second DATA step--avoiding any reorganizing the data to sort it. Without the data set option, the second DATA step has to make a temporary copy of the table--partitioning and sorting it.)

Re: minimum values of column by group in cas data

improvingCas — Wed, 01 Aug 2018 15:37:37 GMT

thanks, it works!!!!

you save my life

thank you for your help !!!!