topic Re: How do I Select certain list of data from separate list during export to excel? in New SAS User

How do I Select certain list of data from separate list during export to excel?

jsem — Mon, 01 Feb 2021 17:39:31 GMT

Hi, I'm using SAS 9.4 and I'd like to use the above code but filter out (in some way) a specific list of SUBNUM from each table. i.e. the SUBNUM list (below, right now about 8, but up to about 15) are the only SUBNUM I want in each tab, but I'd like all records for each SUBNUM in the list. The SUBNUM variable is in every dataset. Not all datasets will have records for each SUBNUM from the list, and some tables will have more than one record per SUBNUM from the list. Thank you so much for your help.

libname ABC 'X:\Folder\SASData';

data _null_;
     call symputx('datestamp', put(date(), YYMMDDN.));
run;

libname XL xlsx "%sysfunc(pathname(WORK)) \..\..\..\..\..\Filename Output &datestamp..xlsx";
run;

proc copy inlib=ABC outlib=XL mt=data; 
run;

SUBNUM

123

345

Example data from one dataset that I want to filter for SUBNUM '123' and '345':

Column1	SUBNUM	Column2	Column3
data	123	data	data
data	345	data	data
data	789	data	data
data	111	data	data
data	123	data	data
data	789	data	data
data	123	data	data

Re: How do I Select certain list of data from separate list during export to excel?

Tom — Mon, 01 Feb 2021 17:50:07 GMT

Just use a DATA step instead of PROC COPY.

data XL.dataset1 ;
  set ABC.dataset1 ;
  where subnum in (123 456);
run;

You will just need to use some code generation to create a separate data step for each member in ABC.

proc contents data=abc._all_ noprint out=contents; run;
filename code temp;
data _null_;
  set contents ;
  by memname;
  if first.memname then subnum=0;
  if lowcase(name)='subnum' then subnum=1;
  if last.memname;
  file code ;
  put 'data xl.' memname '; set abc.' memname ';' ;
  if subnum then put 'where subnum in (123 456);' ;
  put 'run;';
run;
%include code / source2;

Re: How do I Select certain list of data from separate list during export to excel?

jsem — Mon, 01 Feb 2021 18:20:48 GMT

Hi Tom, thanks so much! Do I need to change anything in the code besides dataset1 and 123 456? e.g. is memname = dataset1? Just checking 🙂 Thanks again!

Jenn

Re: How do I Select certain list of data from separate list during export to excel?

Tom — Mon, 01 Feb 2021 19:16:09 GMT

If the list of SUBNUM value is short enough then put it into a macro varaible.

%let subnum_list=123 456 ;
...
where subnum in (&subnum_list)
...

If the list of SUBNUM values is large then keep it in a dataset and generate different code.

For example you could copy one dataset with SQL code like this:

create table XL.dataset1 as 
  select a.* 
  from ABC.dataset1 a 
  where a.subnum in (select b.subnum from SUBNUM_LIST b )
;

Re: How do I Select certain list of data from separate list during export to excel?

jsem — Mon, 08 Feb 2021 20:57:02 GMT

Hi Tom, thanks so much! The SUBNUM list isn't very long (less than 15 unique), it's the dataset list that's long. There's 85 datasets and I'm concerned that having this code for 85 datasets would be a strain. Also, it took me a bit but I figured out that my data thinks the SUBNUM are char (oops). Thanks for your continued support!

data XL.dataset1 ; set ABC.dataset1 ; where subnum in (123 456); run;

Jenn

PS I loved the idea for the let subnum_list, it worked like a charm!

%let subnum_list=123 456 ;