topic Re: Proc SQL in SAS Programming

Proc SQL

jacksonan123 — Thu, 07 Nov 2019 16:10:01 GMT

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

Re: Proc SQL

Cynthia_sas — Thu, 07 Nov 2019 16:10:14 GMT

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

Re: Proc SQL

Reeza — Thu, 07 Nov 2019 16:11:37 GMT

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.