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

PROC SQL LOOP (Inserting data in an existing table)

01SASUser — Thu, 10 Apr 2014 09:42:17 GMT

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

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

RW9 — Thu, 10 Apr 2014 09:52:56 GMT

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;

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

SKK — Thu, 10 Apr 2014 11:04:00 GMT

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;

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

RW9 — Thu, 10 Apr 2014 12:12:02 GMT

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;

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

01SASUser — Fri, 11 Apr 2014 05:04:53 GMT

Thank you for your suggestion. It worked!

I have an additional question.

I tried to revise the code a little by changing

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

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

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

SKK — Fri, 11 Apr 2014 06:10:46 GMT

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;

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

SKK — Fri, 11 Apr 2014 07:33:08 GMT

I think your date is in character format. try

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

It will work.

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

01SASUser — Fri, 11 Apr 2014 07:37:15 GMT

It is now working. Thank you for you support!