BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
01SASUser
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
SKK
Calcite | Level 5 SKK
Calcite | Level 5

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5 SKK
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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?

SKK
Calcite | Level 5 SKK
Calcite | Level 5

%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
Calcite | Level 5 SKK
Calcite | Level 5

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
Fluorite | Level 6

It is now working. Thank you for you support!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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