Putting dataset names into a list but only for certain criteria

[Sean_OConnor]

I have the following datasets in a list;

		proc sql noprint;
		select   distinct  MEMNAME 
			into    :MEM_LIST separated by " "
				from    DICTIONARY.COLUMNS
					where   LIBNAME="PMODA";
quit;

%put &MEM_LIST;
APR_19 APR_20 AUG_19 DEC_19 FEB_19 FEB_20 JAN_19 JAN_20 JUL_19 JUN_19 MAR_19 MAR_20 MAY_19 NOV_19 OCT_19 SEP_19 YEAR_TO_DATE_19

However, I want to only have datasets which end in _19 in my variable called mem_list.

I've tried to use and memname like "19%" in my sql query but this does appear to pick it up.

Can anyone provide some suggestions, please?

[novinosrin · Accepted solution]

Hi @Sean_OConnor  How about using SCAN

where   LIBNAME="PMODA" and scan(memname,-1,'_')='19';

[Sean_OConnor]

That does the trick nicely!

[Sean_OConnor]

Sorry, a quick further questions. Is there an easy way to include pmoda. before each mename? So when I look at my list it would be like pmoda.apr_19 pmod.aug_19

[novinosrin]

Well if you are filtering using LIBNAME='PMODA' , you would only have those datasets in that library.

Hmm, I am wondering whether you are asking to concatenate the libname with memname? Is this correct?If yes, you may want to do this-   

select distinct catx('.',libname,MEMNAME) as memname

	select   distinct  catx('.',libname,MEMNAME) as memname 
			into    :MEM_LIST separated by " "
				from    DICTIONARY.COLUMNS
					where   LIBNAME="PMODA";

[Sean_OConnor]

Thank you!