topic Re: dynamically selecting variables in SQL in SAS Programming

dynamically selecting variables in SQL

Jody_H — Fri, 02 Aug 2019 17:35:09 GMT

I'm trying to consolidate some code. Currently, I have:

For group X:

PROC SQL;

select Var_A, Var_B, Var_C

from mytable;

For group Y:

Proc SQL;

Select Var_A, Var_C, Var_D

from mytable;

is there a way to say;

Proc SQL;

Select [variables contingent on group]

from mytable;

FYI-using SAS EG 7.15 with connection to Hadoop data.

My actual data has 50 groups and over 100 variables, so I'd like to make this as dynamic as possible. The key point is I don't want all of my variables for all of my groups. Thanks.

Re: dynamically selecting variables in SQL

PaigeMiller — Fri, 02 Aug 2019 17:55:42 GMT

Is group some variable in the dataset/database?

How would someone writing this code know what variables are to be extracted for each group? (We can't give you dynamic code based on your two groups shown)

What is the desired output? One SAS data set, many SAS data sets, or printed/html results?

Re: dynamically selecting variables in SQL

PGStats — Fri, 02 Aug 2019 17:50:54 GMT

Do you need something more "dynamic" than CASE WHEN expressions?

Re: dynamically selecting variables in SQL

noling — Fri, 02 Aug 2019 17:55:54 GMT

You could do something like this:

Create a macro variable for each group
1. Using numbers instead of letters makes it easier to iterate
Use a %do loop to run all your sql queries sequentially

You would somehow need to get your variables into their group lists. Copy/paste from excel?

%let group1=Var_A, Var_B, Var_C;
%let group2=Var_A, Var_C, Var_D;
/*...*/

%let table=mytable;
%let num_groups=2;

%macro loop;
	proc sql;
		%do i = 1 %to &num_groups;
			select &&group&i
			from &table;
		%end;
	quit;

%mend;
%loop

*testing;
%let group1=make, model;
%let group2=make, type;
%let table=sashelp.cars;
%let num_groups=2;
%loop

Re: dynamically selecting variables in SQL

Jody_H — Fri, 02 Aug 2019 18:00:17 GMT

Yes, group is a variable in the dataset.

The goal is separate datasets for each group with the appropriate variables in each group.

Thanks for the responses so far, based on the number of groups and number of variables, 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.

Re: dynamically selecting variables in SQL

noling — Fri, 02 Aug 2019 18:13:28 GMT

At some point you or a system will have to specify which variables are in which groups. You could have this process be driven by a csv file or other flat file - 1 record per group with each field holding a variable in that group. This would help maintain your groups for changes. Read that csv file into SAS, then dynamically create the group macro variables each containing their list of fields. Then pass those group macro variables into your sql query.

Re: dynamically selecting variables in SQL

PaigeMiller — Sat, 03 Aug 2019 11:22:43 GMT

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