You could use macro logic to get the list of variables in advance.
data ds1;
input ID pair type $ time_3 time_6 time_9 time_18;
datalines;
1 1 a 111 134 . .
2 1 b 110 . 123 .
3 2 a . 131 . 120
4 2 b . . . 123
;
run;
data ds2;
input ID pair type $ time_3 time_9 time_15;
datalines;
1 1 a 111 134 .
2 1 b 110 . 123
3 2 a . 131 .
4 2 b 125 . .
;
run;
%macro check(dsn);
proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where libname="WORK" and memname="%upcase(&dsn)"
and upcase(name) like "TIME_%";
quit;
%put &varlist;
%let num=%sysfunc(countw(&varlist,%str( )));
%put #
proc sql;
create table counts as
select ID, pair, type,
%do i=1 %to #
%if &i ne &num %then %do;
not missing (%scan(&varlist,&i,%str( )) ) as %scan(&varlist,&i,%str( )),
%end;
%else %do;
not missing (%scan(&varlist,&i,%str( )) ) as %scan(&varlist,&i,%str( ))
%end;
%end;
from &dsn;
quit;
%mend;
options mprint mlogic symbolgen;
%check(ds2)
proc print data=counts;
run;
... View more
