/*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;
I think your date is in character format. try
%DO k= %sysevalf("%trim(&raw_date)"d) %TO %eval(%sysfunc(today())-1);
It will work.
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;
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;
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;
Thank you for your suggestion. It worked!
I have an additional question.
%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?
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;
I think your date is in character format. try
%DO k= %sysevalf("%trim(&raw_date)"d) %TO %eval(%sysfunc(today())-1);
It will work.
It is now working. Thank you for you support!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: