The following datasets are two examples. The proc sql determines whether there is a value or nor under each variable starting with time_. My problem is that we have lots of datasets to evaluate and 20 variables as time_. 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;
proc sql;
create table counts as
select ID, pair, type,
not missing (time_3) as time_3,
not missing (time_6) as time_6,
not missing (time_9) as time_9,
not missing (time_18) as time_18
from ds1;
quit; The way proc sql is now coded, you can only include the variables available in the dataset of interest (in the above proc sql is for ds1). Is there a way to include a condition that will only run the statement "not missing..." if the variable exists? This way we can include all possible variables starting with time_ at once. Like the following (which include time_12 and time_15 not available in ds1): proc sql;
create table counts as
select ID, pair, type,
not missing (time_3) as time_3,
not missing (time_6) as time_6,
not missing (time_9) as time_9,
not missing (time_12) as time_12,
not missing (time_15) as time_15,
not missing (time_18) as time_18
from ds1;
quit;
... View more
