topic Re: Putting dataset names into a list but only for certain criteria in SAS Programming

Putting dataset names into a list but only for certain criteria

Sean_OConnor — Thu, 26 Mar 2020 16:08:23 GMT

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?

Re: Putting dataset names into a list but only for certain criteria

novinosrin — Thu, 26 Mar 2020 16:15:46 GMT

Hi @Sean_OConnor How about using SCAN

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

Re: Putting dataset names into a list but only for certain criteria

Sean_OConnor — Thu, 26 Mar 2020 16:18:28 GMT

That does the trick nicely!

Re: Putting dataset names into a list but only for certain criteria

Sean_OConnor — Thu, 26 Mar 2020 16:33:30 GMT

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

Re: Putting dataset names into a list but only for certain criteria

novinosrin — Thu, 26 Mar 2020 16:37:36 GMT

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";

Re: Putting dataset names into a list but only for certain criteria

Sean_OConnor — Thu, 26 Mar 2020 16:43:05 GMT

Thank you!