topic Re: "Like" function in SAS Programming

"Like" function

SASdevAnneMarie — Mon, 07 Mar 2022 14:00:19 GMT

Hello Experts,

I'm looking in my data LB_DOS for the rows with : "xxxxxx 31/12/2021". The macro variable &DatePubli2. has the value 31/12/2021, but my code doesn't work :

%put test &DatePubli2.;  /***test 31/12/2021***/


data DOSS3;
	set DOSS2;
	where LB_DOS like '%&DatePubli2.';
run;

Nor this :

data DOSS3;
	set DOSS2;
	if  prxmatch("m/&DatePubli2./oi",LB_DOS)> 0 
run;

Thank you for your help !

Re: "Like" function

PaigeMiller — Mon, 07 Mar 2022 14:05:58 GMT

We need to know the values of LB_DOS in data set DOSS2, and how this variable is formatted. If the variable is formatted as DDMMYY. or similar, you can't use LIKE because that works on character strings and your data variable is not a character string but a numeric. But that's a guess by me, so ...

Please show us typical values of LB_DOS in data set DOSS2, and also PROC CONTENTS output for this variable.

Re: "Like" function

SASdevAnneMarie — Mon, 07 Mar 2022 14:10:50 GMT

Thank you for the message. The values in the column are
LB_DOS (1 column, 3 rows for example)
Test on 31/12/2021
Test on 09/12/2021
Test on 01/12/2009

Re: "Like" function

PaigeMiller — Mon, 07 Mar 2022 14:20:09 GMT

What does PROC CONTENTS show you about this variable in data set DOSS2?

Re: "Like" function

SASdevAnneMarie — Mon, 07 Mar 2022 14:22:50 GMT

Thank you for the message. The result of proc contents is : LB_DOS Char 50 $50. $50. LB_DOS

Re: "Like" function

PaigeMiller — Mon, 07 Mar 2022 14:31:17 GMT

Use double quotes

	where LB_DOS like "%&DatePubli2";

Macro variables do not resolve inside of single quotes.

Re: "Like" function

SASdevAnneMarie — Mon, 07 Mar 2022 14:40:11 GMT

But unfortunately, I have 0 lines ..

Re: "Like" function

PaigeMiller — Mon, 07 Mar 2022 14:46:17 GMT

Show us the log, include the code as it appears in the log. Copy the log as text and use the </> icon to include the log in your reply.

From now on, when code isn't working properly, show us the log so we can see the code in the log and any ERRORs, WARNINGs and NOTEs. Don't make us ask you to show us the log; do it automatically from now on when code isn't working.

Re: "Like" function

SASdevAnneMarie — Mon, 07 Mar 2022 14:52:44 GMT

28         data DOSS3;
29         	set DOSS2;
30         	where LB_DOS like "%&DatePubli2";
SYMBOLGEN:  Macro variable DATEPUBLI2 resolves to 31/12/2021
31         run;

NOTE: There were 0 observations read from the data set WORK.DOSS2.
      WHERE LB_DOS like '%&DatePubli2';
NOTE: The data set WORK.DOSS3 has 0 observations and 31 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: "Like" function

Tom — Mon, 07 Mar 2022 15:13:48 GMT

Since the field is 50 bytes long and you are only using a wildcard at the start then perhaps there are invisible characters after the date string. Such as TAB, CR, LF , Non-breaking space, null character.

Does it help to add the % after the date?

Or perhaps the macro variable has leading spaces. That can happen if you use the older CALL SYMPUT() function instead of the modern CALL SYMPUTX() function to create the macro variable. It also also happen if you use PROC SQL INTO without the TRIMMED keyword. The message generated by the SYMBOLGEN option will not show the leading spaces. To see them use %PUT and add some visible characters before and after the macro variable reference.

%put |&DatePubli2|;

Re: "Like" function

PaigeMiller — Mon, 07 Mar 2022 15:30:14 GMT

I don't have your data, and so can't test the code against your data. This works for me when I try to find records that have NAME end with ne from SASHELP.CLASS.

%let char=ne;

data a;
    set sashelp.class;
    where name like "%nrstr(%%)&char";
run;

So please try the %nrstr(%%) in your code to replace the single % sign before macro variable &datepubli2.

If that's not it either, then we need to see a portion of data set DOSS2 provided according to these instructions. Many people just ignore the instructions when I mention it. Please do not provide the data in any other manner, as that won't help, and we'll just ask you to follow the instructions again.