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.

Are you using SQL in SAS because you don't know SAS, or are you doing it because you think it's be best approach here? This doesn't seem like a good fit for SQL to me. Data step would be much easier. — Joe, Dec 16 '14 at 17:53
Not a recommended way to go about this, perhaps post what you have versus what you need. This forum may not be appropriate for that type of question, but you can post on communities.sas.com as well. — Reeza, Dec 16 '14 at 22:08
I know both SAS and SQL to a certain extent - as you can see, I'm far away from calling myself a professional though. Still learning. I'm using SQL in SAS because in this case it seemed more convenient to me to create static frames as empty tables first and filling them afterwards than doing everything in a data step. Once the tables are filled, I would use the UPDATE statement to calculate the rates within the tables using simple formulas. I did this before, so at least I know this works. Maybe I should think about other methods once more. Like the one proposed as answer... — s.erhardt, Dec 17 '14 at 08:15
I've removed a dead image from this question. It looks like it still makes sense without that, but please do always use the official uploader, so that images live on in perpetuity. Thanks! — halfer, Jul 07 '17 at 12:30

score 1 · Accepted Answer · answered Dec 16 '14 at 20:19

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;

SAS Populate table with PROC SQL INSERT statement and do loop

1 Answers1