programming within proc cas

[acordes]

I'm creating my own cas actions, and the result is as expected except that the loc function sometimes returns -1 despite the stripped value should be found in the stripped lookup table.

I've created a simplified version of my challenge leaving only the cas actions result part with the loc functionality.  

Here is the link to the documentation. https://documentation.sas.com/doc/en/pgmsascdc/v_062/proccas/p01s21os7idn2ln1aaefthmtjqru.htm 

%let namer=ts;
%let namer2=a123456789b123456789c123456789;
%let namer3=a123456789b123456789c123456789d1;

data casuser.cars;
set sashelp.cars;
format &namer &namer2 datetime20. ;
&namer=intnx('dtminutes', datetime(), rand('integer', -1000, 0));
&namer2=&namer;
&namer3=put(rand('uniform', 0, 1), best12.);
run;

%let casy=cars;
%let liby=casuser;


cas mySession sessopts=(caslib="&liby");

ods escapechar='^';
proc cas;

	table.columninfo result=r /table="&casy";
      	columns = '';
		search_cols='';
      	do i = 1 to r.columninfo.nrows;
			if index(UPCASE(r['columninfo'][i,7]), 'DATETIME')  THEN do;
         		symput('col', ifc(prxmatch('m/\s/i',r[1,i].column), '''||r[1,i].column||''n', r[1,i].column));
         		columns = columns||' '||symget('col');
         		search_cols = strip(search_cols||'|'||symget('col'));
			end;
      end;
      	symput('ok_list', substrn(search_cols,2,length(search_cols)-1));
      	symput('varlist', substrn(columns,2,length(columns)-1));

		print "&ok_list.";
run;

   simple.summary result=sumres / 
      subset={'min', 'max'},
		table="&casy";

	sumlist=findtable(sumres);

do k=1 to dim(sumres.summary);
		print quote(sumres.summary[k, 1]);
end;

    simple.distinct result=dist /
        table="&casy";

do l=1 to dim(dist.distinct);
		print quote(dist.distinct[l, 1]);
end;

run;

	do i=1 to dim(dist.distinct);
			if prxmatch(cats('m/\b', symget('ok_list'), '\b/i'), strip(dist.distinct[i, 1]))  then do;
					help=strip(dist.distinct[i, 1]);
						print quote(help);
					j=loc(sumlist, 'column', help);
			if j<1 then do;
						help=dist.distinct[i, 1];
							print quote(help);
						j=loc(sumlist, 'column', help);
			end;
						print j;
		end;

	end;
run;

And the log shows the problem:

The loc function does not return a value for the variable 'ts'n because the distinct action returns a wider width for the column name than the summary action. 

@BrunoMueller to the rescue 🙂

[acordes]

I've solved it by adding a fake numeric variable whose name has length 32. 

this ensures the loc function working properly. 

[BrunoMueller · Accepted solution]

Glad you found a solution. It seems that the LOC function uses char values and if the length does not match it will not be found.

Another way of doing this, would be to get the values into an array and then use this expression:

newArray = columnNamesArray & lookupArray;

This will return only common values in both arrays, see CASL Array Operrators for more details. the length of the values does not seem to matter. Here is the code I used for testing:

%let namer=ts;
%let namer2=a123456789b123456789c123456789;
%let namer3=a123456789b123456789c123456789d1;

%let casy=cars;
%let liby=casuser;

cas mySession sessopts=(caslib="&liby");
libname casuser cas caslib="casuser";

data casuser.cars;
  set sashelp.cars;
  format &namer &namer2 datetime20. ;
  &namer=intnx('dtminutes', datetime(), rand('integer', -1000, 0));
  &namer2=&namer;
  &namer3=put(rand('uniform', 0, 1), best12.);
run;

proc cas;
  table.columninfo result=r / table="&casy";
  columns = '';
  search_cols='';
  /* array with names to search for */
  search_cols_array = {};
  
  do i = 1 to r.columninfo.nrows;
    if index(UPCASE(r['columninfo'][i,7]), 'DATETIME')  THEN do;
      symput('col', ifc(prxmatch('m/\s/i',r[1,i].column), '''||r[1,i].column||''n', r[1,i].column));
      /* fill array with search values */
      search_cols_array = search_cols_array + r[1,i].column;
      columns = columns||' '||symget('col');
      search_cols = strip(search_cols||'|'||symget('col'));
    end;
  end;
  print(note) "search_cols_array=" search_cols_array;
  symput('ok_list', substrn(search_cols,2,length(search_cols)-1));
  symput('varlist', substrn(columns,2,length(columns)-1));
run;
  %put &=ok_list;
  %put &=varlist;
run;
  simple.summary result=sumres / subset={'min', 'max'}, table="&casy";
/*   describe sumres.summary; */
  sumlist=findtable(sumres);
  colNames = GETCOLUMN(sumlist, "Column");
  print(note) "colNames=" colNames;
  commonArray = colNames & search_cols_array;
  print(note) "commonArray summary=" commonArray;
run;
  simple.distinct result=dist / table="&casy";
  describe dist.distinct;
  colNames = GETCOLUMN(dist.distinct, 1);
  print(note) "colNames=" colNames;
  commonArray = colNames & search_cols_array;
  print(note) "commonArray distinct=" commonArray;
run;
quit;