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
... View more
