PROC SQL LOOP (Inserting data in an existing table)

[01SASUser]

/*There is something wrong in my SAS Program but I can't figure it out. Could you please help me?

SCENARIO:

1. The date of my RAW data is "08APR2014

2. For example the date today is "10APR2014". I want SAS to do the loop below until the max(DATE) of Consolidated becomes TODAY()-1*/

%macro sqlloop;

PROC SQL;

%DO k=date_of_my_raw_data %TO today()-1;

insert into Consolidated

     (BRANCH,

     RC_NAME,

     DATE)

select

     BRANCH,

     RC_NAME,

     &k.

from RAW;

%END;

QUIT;

%mend;

%sqlloop;

[RW9]

Hi,

Not sure why you are trying to do it like that.  Just use a datastep:

data want;

  attrib my_date format=date9.;

  do i='08apr2014'd to today();

    my_date=i;

    output;

  end;

run;

[SKK]

Hi

Place the Proc SQL inside do loop and evaluate today value using % sysfunc and date_of_my_raw_data using %sysevalf. Suppose if ur raw data date is 08APR2014 then below program would work.

%macro sqlloop;

%DO k= %sysevalf("08APR2014"d) %TO  %eval(%sysfunc(today())-1) ;

PROC SQL;

insert into Consolidated

     (BRANCH,

     RC_NAME,

     DATE)

select

     BRANCH,

     RC_NAME,

     &k.

from RAW;

QUIT;

%END;

%mend;

%sqlloop;

[RW9]

Just to note, if you really had to do it that way, then you don't need the macros at all:

data want;

  do i='08apr2014'd to today();

    call execute('proc sql;

                    insert into consolidated (BRANCH,RC_NAME,DATE)

                    select BRANCH,RC_NAME,'||strip(put(i,date9.)||'

                    from   RAW;

                  quit;');

  end;

run;

[01SASUser]

Thank you for your suggestion. It worked!

I have an additional question.

1. I tried to revise the code a little by changing

%DO k= %sysevalf("08APR2014"d) %TO  %eval(%sysfunc(today())-1);

To

%DO k= %sysevalf(RAW.date) %TO  %eval(%sysfunc(today())-1);

However, I am receiving an error. May I know the correct script for this one?

The value of RAW.DATE that I receive daily is same for all rows (ex. The result of proc sql; Select distinct(DATE) from raw; quit; will only be “08APR2014”) that’s why I would like to revise %sysevalf("08APR2014"d) to %sysevalf(RAW.date).

2. I did the program in SAS DI but the execution is not successful:

The code inside the “User Written” is the script you suggested with little revision(highlighted in bold):

%let output= &_output;

%let MySYSLast= &SYSLast;

%macro sqlloop;

%DO k= %sysevalf("08APR2014"d) %TO  %eval(%sysfunc(today())-1);

PROC SQL;

insert into &output

.

.

select

      BRANCH_CD,

.

.

from &MySYSLast;

QUIT;

%END;

%mend;

%sqlloop;

May I know how to do this correctly in SAS DI?

[SKK]

Hi

Am not sure how to do it in DI, As I understood you have to extract the start date from raw dataset and enter the value to the do loop, In that case you can accomplish it in many ways, Include the proc sql inside ur macro as shown below:

%macro sqlloop;

Proc SQL;

Select distinct(DATE) into :raw_date from raw;

Quit;

%DO k= &raw_date %TO  %eval(%sysfunc(today())-1);

PROC SQL;

insert into &output

.

.

select

      BRANCH_CD,

.

.

from &MySYSLast;

QUIT;

%END;

%mend;

%sqlloop;

[SKK · Accepted solution]

I think your date is in character format. try

%DO k= %sysevalf("%trim(&raw_date)"d)  %TO  %eval(%sysfunc(today())-1);

It will work.

[01SASUser]

It is now working. Thank you for you support!