@Jody_H wrote:
I was hoping to avoid coding a list of variables for each group. I was thinking I could have a table of variables and groups and passing information into the SQL, but wasn't sure how to pass that information into the SQL.
If, for example, you have a text file that shows all 50 groups, and the variables needed from each group, then yes this could be made dynamic. Your read the text file into a SAS data set first. Then, it would either require a macro or CALL EXECUTE.
Here is a macro solution, where the input text file has the group number separated by a space and then all the desired variable name(s), separated by space(s). I use data set ABC to hold this text information.
data abc;
infile cards truncover;
input string $200.;
cards;
1 temperature wind rain
18 homerun single double triple
;
data _null_;
set abc end=eof;
string=compbl(string);
group=scan(string,1);
call symputx('group'||left(_n_),cats('group',group));
call symputx('text'||left(_n_),translate(trim(left(substr(string,length(group)+1))),',',' '));
if eof then call symputx('nrows',_n_);
run;
%macro dothis;
proc sql;
%do i=1 %to &nrows;
create table &&group&i as select &&text&i from yourdatabase;
%end;
quit;
%mend;
options mprint;
%dothis
options nomprint;
... View more
