Pass a SAS array value to Proc SQL as a field name

[Phuels]

Hello,

I'm a fairly new user of SAS and am hoping you can help with a SAS 9.4 question. I usually use SAS EG, so am just learning how to program in SAS 9.4.

I have a list of 19 columns that I'd like to pull from a db2 data server. The column names are identical except for the suffix. I'd like to use a loop to pull the data rather than building 19 different queries, but am not sure how to do this.

I've looked through about 20 or so posts and found that I need to create arrays to house the column names...

data define_arrays;
	array cov_list(19) $ a g d s h g m n b p q r t ua ub wa wb y z;
	array wp_list(19) $ ;
	array asln_list(19) $ ;
	do i - 1 to 19;
		wp_list(i) = 'prem_wp_' & cov_list(i);
		asln_list(i) = 'asln_' & cov_list(i);
        end;
run;

My understanding is that I need to create a blank table with the desired field names...

proc sql;
create table WORK.asln_prem 
	(State char(20),
	 Cov char(2),
	 asln num,
	 prem_ep_total num);

I have a working SQL query to pull the data...

proc sql outobs=;
connect to db2 (database='ACTDM5');

create table WORK.asln_prem as 

select * 

from connection to db2
(

SELECT	CASE
		WHEN state IN ('05','55','75') THEN 'California'
	        WHEN state IN ('43','53') THEN 'Texas'
	        WHEN state IN ('32','52') THEN 'New York'
	        WHEN state = '09' THEN 'District of Columbia'
	        ELSE initcap(u92.statename_u(state))
	END AS STATE, 
	double(sum(u92.Ep_auto_annual_u(date_effective_date, date_expiration_date, date_accounting_date, Integer(201712), prem_wp_a))) as prem_ep_total, 
	asln_a as asln,
	'a' as Cov
FROM	v23.auto_prem_v 
WHERE	state not IN( '60','61','64' ) AND
	business_group LIKE 'B01%' AND
	YEAR IN ( 2017, 2016 ) 
GROUP BY 
	state, 
        asln_a 

); 
disconnect from db2;

quit;

I need to modify the above code to:

• use the array values in place of the static field names/values
  • asln_a replaced by asln_list(i) and used as a field name
  • prem_wp_a replaced by prem_wp_list(i) and used as a field name
  • 'a' replaced by cov_list(i) and used as a field value
• append the data pull to the existing table (work.asln_prem) rather than creating the table
• run the proc sql 19 times

I appreciate any help you can provide that will help me make this work. If there's a better approach to take, I'm an eager learner.

Thanks in advance,

Frank

[Kurt_Bremser]

Arrays are part of data step syntax, they cannot be used in SQL.

And a select * will pull all columns, anyway.

[Tom]

I cannot figure out what your question is.  Please show what code you are trying to generate.

I think you are asking for help writing something like:

...
prem_wp_a,prem_wp_g,prem_wp_d,prem_wp_s
,prem_wp_h,prem_wp_g,prem_wp_m,prem_wp_n
,prem_wp_b,prem_wp_p,prem_wp_q,prem_wp_r
,prem_wp_t,prem_wp_u,prem_wp_u,prem_wp_w
,prem_wp_w,prem_wp_y,prem_wp_z
,asln_a,asln_g,asln_d,asln_s,asln_h,asln_g,asln_m
,asln_n,asln_b,asln_p,asln_q,asln_r,asln_t,asln_u
,asln_u,asln_w,asln_w,asln_y,asln_z
...

[Phuels]

Thanks for the responses. it sounds like I can't pass values from a SAS array into an SQL statement. 

 Here's what I want to do...

Create a table (this works)

proc sql;
create table WORK.asln_prem 
	(State char(20),
	 Cov char(2),
	 asln num,
	 prem_ep_total num);

 Pull data from an external source and pend it to the table (this also works)

proc sql outobs=;

connect to db2 (database='ACTDM5');

insert into work.asln_prem 

select * 

from connection to db2
(

SELECT	CASE
		WHEN state IN ('05','55','75') THEN 'California'
	        WHEN state IN ('43','53') THEN 'Texas'
	        WHEN state IN ('32','52') THEN 'New York'
	        WHEN state = '09' THEN 'District of Columbia'
	        ELSE initcap(u92.statename_u(state))
	END AS STATE, 
	'a' as Cov,
	double(sum(u92.Ep_auto_annual_u(date_effective_date, date_expiration_date, date_accounting_date, Integer(201712), prem_wp_a))) as prem_ep_ttl, 
		int(coalesce(nullif(asln_a,''),'0')) as asln

FROM	v23.auto_prem_v 
WHERE	state not IN( '60','61','64' ) AND
	business_group LIKE 'B01%' AND
	YEAR IN ( 2017, 2016 ) 
GROUP BY 
	state, 
        asln_a 
); 
disconnect from db2;

quit;

Run the above query 19 times using the following values...

Iteration     Cov_code   asln_code   prem_wp_code

1                a                 asln_a         perm_wp_a

2                g                 asln_g         prem_wp_g

...

19              z                 asln_z         prem_wp_z

The field values above would replace the fields in the SQL query...

• Cov_code replaces 'a' and the SQL uses it as a static value
• asln_code replaces asln_a and the SQL uses it as a field name to query the external data source and group the data
• prem_ep_code replaces prem_ep_a and the SQL uses it as a field name to query the external data source

Does this make more sense?

Thanks for your help,

Frank

[Reeza]

Please show two versions of your program and hightlight the changes between the two. Not seeing it at the moment. 

[Phuels]

The current version is in my reply. the revised version would look like this...

proc sql outobs=;

connect to db2 (database='ACTDM5');

insert into work.asln_prem 

select * 

from connection to db2
(

SELECT	CASE
		WHEN state IN ('05','55','75') THEN 'California'
	        WHEN state IN ('43','53') THEN 'Texas'
	        WHEN state IN ('32','52') THEN 'New York'
	        WHEN state = '09' THEN 'District of Columbia'
	        ELSE initcap(u92.statename_u(state))
	END AS STATE, 
	Cov_code as Cov, /* value of cov_code used as value */
	double(sum(u92.Ep_auto_annual_u(date_effective_date, date_expiration_date, date_accounting_date, Integer(201712), prem_wp_code))) as prem_ep_ttl, /*value of prem_wp_code used as field name */
		int(coalesce(nullif(asln_code,''),'0')) as asln /* value of asln_code used as field name */

FROM	v23.auto_prem_v 
WHERE	state not IN( '60','61','64' ) AND
	business_group LIKE 'B01%' AND
	YEAR IN ( 2017, 2016 ) 
GROUP BY 
	state, 
        asln_code /* field name */
); 
disconnect from db2;

quit;

[ballardw]

I think this builds the work.asln_prem empty data set, if I understand what you want.

data _null_;
   call execute ("proc sql; create table work.asln_prem
                ( state char(20), cov char(2)");
   cov =  'a g d s h g m n b p q r t ua ub wa wb y';
   stem = 'Prem_wp asln_';
   length varname $ 32;
   do j=1 to countw(stem);
      do i= 1 to countw(cov);
         varname= catx(' ',",",cats(scan(stem,j),scan(cov,i)),'num');
         call execute (varname);

      end;
   end;
   call execute(" ); quit;");
run;

CALL EXECUTE places lines of code in a buffer to execute after the data step writing them quits.

Sequence on when things like commas get inserted is somewhat fun. Also note the end of the proc sql after all of the names are created has to close the ) and provide a statement ending ; plus the quit;

[Phuels]

Hello and thanks again for all of the responses. I guess I still am not being clear on what I'm trying to accomplish.

In simplest terms, I have a list of 19 suffixes and I want to run the SQL query once for each suffix. The SQL should use the suffix as a field value in one column, and use it as the column name in two of the columns. The end result should be a single table with four columns. As an example, if the suffix is UA then the query should return the following

State          Cov        PREM_EP_Total         ASLN

California     UA         value of PREM_EP_UA   value of ASLN_UA

State, PREM_EP_UA, and ASLN_UA are all field names in the query data source.

The information on the call execute statement really helped me to understand how it was used. I wound up creating something that almost works the way I'd like...runs the query 19 times and creates 19 output tables. I wanted to have all of the information in a single table, but kept getting the following:

     ERROR: You cannot open WORK.ASLN_PREM.DATA for output access with member-level control because

     WORK.ASLN_PREM.DATA is in use by you in resource environment .

I imagine there's a way to do it, but not savvy enough to figure it out. For now, I'm using a separate append function to add all of the tables together.

[Phuels]

Here's a working version of the code that creates the 19 tables. I didn't bother to include the Cov_List datalines

Data _null_;

/* Set static SQL string values */

SQL_Start = "proc sql outobs=;
	connect to db2 (database='ACTDM5');";

SQL_0 = "create table work.asln_";

SQL_1 = " as select * from connection to db2
	( SELECT	CASE
			WHEN state IN ('05','55','75') THEN 'California'
	        WHEN state IN ('43','53') THEN 'Texas'
	        WHEN state IN ('32','52') THEN 'New York'
	        WHEN state = '09' THEN 'District of Columbia'
	        ELSE initcap(u92.statename_u(state))
	END AS STATE, '";

SQL_2 = "' as Cov,
		double(sum(u92.Ep_auto_annual_u(date_effective_date, date_expiration_date, date_accounting_date, Integer(201712), prem_wp_";

SQL_3 = "))) as prem_ep_total, 
		int(coalesce(nullif(asln_";

SQL_4 = ",''),'0')) as asln
	FROM	v23.auto_prem_v 
 	WHERE	state not IN( '60','61','64' ) AND
			business_group LIKE 'B01%' AND
			YEAR IN ( 2017, 2016 )
	GROUP BY state, asln_";

SQL_5 = "); ";

SQL_End = "disconnect from db2;
	quit;";

Set Cov_List end=end_of_data;
SQL_Cov=strip(Cov_T);
call execute( 
	strip(SQL_Start)||
	strip(SQL_0)||SQL_Cov||
	strip(SQL_1)||SQL_Cov||
	strip(SQL_2)||SQL_Cov||
	strip(SQL_3)||SQL_Cov||
	strip(SQL_4)||SQL_Cov||
	strip(SQL_5)||
	strip(SQL_End)
			);	
run;

[Phuels · Accepted solution]

Here's the version that generates the error

Data _null_;

/* Set static SQL string values */

SQL_Start = "proc sql outobs=;
	connect to db2 (database='ACTDM5');
	create table work.asln_prem as ";

SQL_1 = "select * from connection to db2
	( SELECT	CASE
			WHEN state IN ('05','55','75') THEN 'California'
	        WHEN state IN ('43','53') THEN 'Texas'
	        WHEN state IN ('32','52') THEN 'New York'
	        WHEN state = '09' THEN 'District of Columbia'
	        ELSE initcap(u92.statename_u(state))
	END AS STATE, '";

SQL_2 = "' as Cov,
		double(sum(u92.Ep_auto_annual_u(date_effective_date, date_expiration_date, date_accounting_date, Integer(201712), prem_wp_";

SQL_3 = "))) as prem_ep_total, 
		int(coalesce(nullif(asln_";

SQL_4 = ",''),'0')) as asln
	FROM	v23.auto_prem_v 
 	WHERE	state not IN( '60','61','64' ) AND
			business_group LIKE 'B01%' AND
			YEAR IN ( 2017, 2016 )
	GROUP BY state, asln_";

SQL_5 = ") ";

SQL_Connect = " outer union ";

SQL_End = "; 
	disconnect from db2;
	quit;";

/* initiate the SQL on the first iteration */
if _n_ = 1 then call execute(SQL_Start);

/* disconnect SQL on the last iteration */
else if end_of_data then 
	do;
		call execute(SQL_End);
		stop;
	end;

/* insert union statement between iterations */
else call execute(SQL_Connect);

set Cov_List end=end_of_data;
SQL_Cov=strip(Cov_T);
call execute( 
	strip(SQL_1)||SQL_Cov||
	strip(SQL_2)||SQL_Cov||
	strip(SQL_3)||SQL_Cov||
	strip(SQL_4)||SQL_Cov||
	strip(SQL_5)
			);	
run;

[Phuels]

Should have checked the error before posting, as it simply means that the table is still open in the buffer. Closing out of SAS and restarting my system cleared the buffer, so now it work.

Thanks again everyone for your help.

[Tom]

It would help if you explained in words what the logic of that query is.

Why is it grouping by a value that is NOT in the SELECT list?

Why is it NOT grouping by a value COV that IS in the select list?

Looks like you could either code this as a series of INSERT statements. Or code it as one large query that UNIONs a series of indvidual sub queries.

One way to simplify that might be to make a little macro that just generates the SELECT statement for one COV value.

%macro one(COV);

SELECT
   CASE WHEN state IN ('05','55','75') THEN 'California'
        WHEN state IN ('43','53') THEN 'Texas'
        WHEN state IN ('32','52') THEN 'New York'
        WHEN state = '09' THEN 'District of Columbia'
        ELSE initcap(u92.statename_u(state))
   END AS STATE
 , %bquote('&COV') as Cov
 , int(coalesce(nullif(asln_&COV,''),'0')) as asln
 , double(sum(
   u92.Ep_auto_annual_u(date_effective_date, date_expiration_date
                       ,date_accounting_date, Integer(201712), prem_wp_&COV)
   )) as prem_ep_ttl
FROM v23.auto_prem_v
WHERE state not IN( '60','61','64' )
  AND business_group LIKE 'B01%'
  AND YEAR IN ( 2017, 2016 )
GROUP BY
  state
, cov
, asln

%mend one;

Then you could use it in either structure.

create table work.asln as
  select * from connection to db2
  ( %one(A) )
;
insert into work.asln
  select * from connection to db2
  ( %one(B) )
;
...

Or 

create table work.asln as
  select * from connection to db2
  ( %one(A) 
     union
     %one(B)
     union .....
)
;

If you wanted you could either then use CALL EXECUTE or macro logic to generate the code above.

But for your little example of 19 names it might just be easier to use the editor to copy and paste and then change the value passed to the macro call.