SAS Populate table with PROC SQL INSERT statement and do loop

Question

This is for an epidemiologic project. I wanted to calculate disease frequency rates in the years 1961 to 2013 among different populations: men of all ages, men over 50 years of age and the same two cases for women.

First, I imported a population table called 'pop_compl', which contains the population numbers in different age classes for both men (sex = 1) and women (sex = 0, no offense) in the mentioned time span.

Then, I created empty tables in SAS with PROC SQL:

proc sql;

create table m_rates (year num(10), population num(10), cases num(10), crude_incidence num(10), esr num(10), esr_95CI_ll num(10), esr_95CI_ul num(10));

create table m_rates_50plus (year num(10), population num(10), cases num(10), crude_incidence num(10), esr num(10), esr_95CI_ll num(10), esr_95CI_ul num(10));

create table w_rates (year num(10), population num(10), cases num(10), crude_incidence num(10), esr num(10), esr_95CI_ll num(10), esr_95CI_ul num(10));

create table w_rates_50plus (year num(10), population num(10), cases num(10), crude_incidence num(10), esr num(10), esr_95CI_ll num(10), esr_95CI_ul num(10));

Now I wanted to fill the first two columns, year and population, of each of these tables above (and later also the third one 'cases') with values to be able to calculate the needed rates within the tables later on. The column years should be filled with the values 1961-2013, the column population with the according population numbers from 'pop_compl' for every year between 1961 and 2013.

I wanted to do that by using an insert statement within a macro and a do loop. Looks like this:

%macro fill(table, sex, age_class);


insert into &table (year, population)
%do year=1961 %to 2013;
    VALUES(&year, (select _&year from pop_compl where sex = &sex and age_class like "&age_class"))
%end;

    ;

%mend;

%fill(m_rates, 1, total);
%fill(m_rates_50plus, 1, > 50);
%fill(w_rates, 0, total);
%fill(w_rates_50plus, 0, > 50);

Although it seems like this is logically correct, SAS complains about using a query within the values statement - excerpt:

1037  %fill(m_rates_50plus, 1, > 50);
NOTE: No rows were updated in WORK.M_RATES_50PLUS.

NOTE: Line generated by the invoked macro "FILL".
3              VALUES(&year, (select _&year from pop_compl where sex = &sex and     age_class like
                             -
                            22
                            76
3   ! "&age_class"))
ERROR 22-322: Syntax error, expecting one of the following: a quoted string,
          a numeric constant, a datetime constant, a missing value, ), +, ',', -,     MISSING,
          NULL, USER.

 ERROR 76-322: Syntax error, statement will be ignored.

I tried several things, changed variable types and so on. Nothing helped, I really think it's a SAS SQL restriction. I'm using SAS 9.2 32bit. At the moment, I don't know how to fix this and I didn't come up with another quick method that does the same.

Answer 1

You can only use SELECT statement in INSERT like this:

INSERT INTO TABLE1 (col1, col2) SELECT col1, col2 from TABLE2 WHERE ...

but not in VALUES clause - there has to be constants:

INSERT INTO TABLE1 (col1, col2) VALUES (123, 123)

You could also create a temp table and append it into the target:

PROC SQL; CREATE TABLE VAL1 AS SELECT ....;QUIT;
PROC APPEND DATA=VAL1 BASE=TABLE1;
RUN;