DATA Step, Macro, Functions and more

PROC SQL LOOP (Inserting data in an existing table)

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

PROC SQL LOOP (Inserting data in an existing table)

/*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;


Accepted Solutions
Solution
‎04-11-2014 03:33 AM
Contributor SKK
Contributor
Posts: 35

Re: PROC SQL LOOP (Inserting data in an existing table)

I think your date is in character format. try

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

It will work.

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: PROC SQL LOOP (Inserting data in an existing table)

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;

Contributor SKK
Contributor
Posts: 35

Re: PROC SQL LOOP (Inserting data in an existing table)

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;

Super User
Super User
Posts: 7,401

Re: PROC SQL LOOP (Inserting data in an existing table)

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;

Contributor
Posts: 22

Re: PROC SQL LOOP (Inserting data in an existing table)

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:

SAS DI.JPG

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?

Contributor SKK
Contributor
Posts: 35

Re: PROC SQL LOOP (Inserting data in an existing table)

%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;

Solution
‎04-11-2014 03:33 AM
Contributor SKK
Contributor
Posts: 35

Re: PROC SQL LOOP (Inserting data in an existing table)

I think your date is in character format. try

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

It will work.

Contributor
Posts: 22

Re: PROC SQL LOOP (Inserting data in an existing table)

It is now working. Thank you for you support!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1349 views
  • 10 likes
  • 3 in conversation