Proc SQL

[jacksonan123]

I have the following code within a macro which goes from 1 to 2.  Example data for ALL_KLMEAN1 and ALL_KLMEAN2 is included with the code.

DATA ALLA_KLMEAN&K;
SET nsum&K;
BY NREP;
IF uclm => 0.79 THEN ANSWER1=1;
IF uclm LT 0.79 THEN ANSWER1=0;
RUN;

option spool;
proc sql number;
CREATE TABLE FINAL AS 
select UCLM, ANSWER1 ,NREP
from ALLA_KLMEAN&K;
QUIT;

Data:                        nrep                  lclm            uclm       Answer1

ALLA_KLMEAN1        1                   -0.003           0.044         0
ALLA_KLMEAN2        2                    -0.004          0.049         0

After the macro is run the table final only contains the results from ALL_KLMEAN2..  I want the final table to contain ALL_KLMEAN2

and ALL_KLMEAN1 results so it would look like this:

nrep         uclm    answer1

1               0.044      0

2               0.049      0

[Cynthia_sas]

Hi:
When &K is 1, you create WORK.FINAL from ALLA_KLMEAN1. But then, when &K is 2, you OVERWRITE or REPLACE WORK.FINAL with the results of the the second PROC SQL. You have to change your approach to get both results into the same dataset. I would probably use PROC APPEND or a DATA step.

Cynthia

[Reeza · Accepted solution]

Well you create the final table each time from the most recent run so that is correct. Append the results to a table at the end if you want to keep it instead of using PROC SQL.

proc append base=final data=alla_klmean&k;
run;

Note that you don't need the k in the data set names from what you've shown, it doesn't help anything if you're just stacking your tables.