topic Re: boucle do with 30 minutes increment in SAS Programming

boucle do with 30 minutes increment

Reality696 — Wed, 05 Apr 2017 11:44:06 GMT

Hi all,

i'm starting SAS today and i don't find a solution to my issue.

FInd my code below :

%macro inter(start, end);
%do i=&start %to &end %by 30*60;

PROC SQL;
CREATE TABLE WORK.MAX_LAST_UPD_TS_DAY AS
SELECT MAX(T5.LAST_UPD_TS)
     FROM Table5 T5
     WHERE put(timepart(T5.LAST_UPD_TS),time.) < put(&i,time.);
QUIT;

PROC SQL;
---
QUIT;

%end;
%mend;

%inter(start="00:00:00"t, end="18:00:00"t);

The errors code returned :

ERROR: Required operator not found in expression: &start
ERROR: The %FROM value of the %DO I loop is invalid.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&end
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro INTER will stop executing.

Thank you for those who will help me 🙂

Re: boucle do with 30 minutes increment

Madansas7b — Wed, 05 Apr 2017 12:42:47 GMT

hi @Reality696,

am not sure but try like bellow .while caling the start and end time

%do i=%sysevalf(&start) %to %sysevalf(&end) %by 30*60;

Re: boucle do with 30 minutes increment

rogerjdeangelis — Wed, 05 Apr 2017 12:47:11 GMT

%macro inter(start, end);
%do i=%sysevalf(&start) %to %sysevalf(&end) %by %eval(30*60);

proc sql;
   create
      table dlass as
   select
      *
   from
      sashelp.class
;quit;

%end;
%mend;

%inter(start="00:00:00"t, end="18:00:00"t);

NOTE: Table WORK.DLASS created, with 19 rows and 5 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds


NOTE: Table WORK.DLASS created, with 19 rows and 5 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: Table WORK.DLASS created, with 19 rows and 5 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

...

Re: boucle do with 30 minutes increment

rogerjdeangelis — Wed, 05 Apr 2017 13:35:39 GMT

May I suggest setting meta data.

data meta;
  input dsn : $18. start time8. end time8.;
cards4;
sashelp.class 00:00:00 18:00:00
sashelp.iris 18:00:00 24:00:00
sashelp.qrtns 18:00:00 24:00:00
;;;;
run;quit;


* Unlike 'call execute and many macro solutions you
  can set whst ever criteria you want to stop the
  execution of the iteration of a macro or 'call execute';


data _null_;

  set meta;

  put dsn=;
  call symputx('symdsn',dsn);

  do tym=start to end by 180*60;

     rc=dosubl('

        proc sql;
           create
              table tmpdat as
           select
              *
           from
              &symdsn
        ;quit;

        data %scan(&symdsn,1,%str(.));
           retain seq 0;
           set tmpdat;
           seq=_n_;
        run;quit;

        %let Errortext= &syserrortext;
        %let Errorcode= &syserr;

        * you can set your own logic for failure;
        * like 0 obs;

     ');

   if symget('errorcode') ne "0"  then do;
      errortext=symget('errortext');
      put "dataset creation failed for " dsn " stopping parent";
      put "Error message " errortext;
      stop;
   end;

 end;
run;quit;

Re: boucle do with 30 minutes increment

ballardw — Wed, 05 Apr 2017 14:39:36 GMT

Starting with macros and not having an understanding of the base SAS language and procedures is almost gauranteed to have much confusion. Macros are intended to generate base code. If you do not know which code you want to generate in the proper forms then macros are going to slow down learning.

For instance, your loop is only going to have a data set for the End value. You overwrite the output set for every value in the do loop leaving only the last one.

Re: boucle do with 30 minutes increment

Reality696 — Wed, 05 Apr 2017 15:28:25 GMT

Exactly what i saw and trying to solve it, adding a new condition to insert the data instead of erasing it 🙂

Re: boucle do with 30 minutes increment

Reality696 — Wed, 05 Apr 2017 15:29:00 GMT

Thank you for the replies guys, it help me 🙂

Re: boucle do with 30 minutes increment

Reality696 — Thu, 06 Apr 2017 09:00:45 GMT

I have another issue since yesterday and i don't have so much time to learn correctly as it's for work (and have no formation on this langage basis)

My code below :

%macro inter(start, end);
%do i=%sysevalf(&start) %to %sysevalf(&end) %by %eval(30*60);


PROC SQL

CREATE TABLE WORK.T1

---

QUIT;


PROC SQL

CREATE TABLE WORK.T2

---

QUIT;


%if &i eq 0 %then
    %do;
 
   PROC SQL;
    CREATE TABLE WORK.T3 as
    SELECT a.BD, a.SDEB, a.SCRED, a.TYPE, a.STATUS,
    (CASE WHEN b.SETT = . THEN a.SETT
      ELSE a.SETT-b.SETT END) As SETT FORMAT=EUROX20.2, a.REMAIN FORMAT=EUROX20.2, a.NB, put(&i,time.) as TS
    FROM WORK.T1 A
        LEFT JOIN WORK.T2 B on (a.BD=b.BD and a.type=b.type and a.status=b.status);
    QUIT;
    %end;
%else
    %do;

    PROC SQL;
    INSERT INTO TABLE WORK.T3
    SELECT a.BD, a.SDEB, a.SCRED, a.TYPE, a.STATUS,
    (CASE WHEN b.SETT = . THEN a.SETT
      ELSE a.SETT-b.SETT END) FORMAT=EUROX20.2, a.REMAIN FORMAT=EUROX20.2, a.NB, put(&i,time.)
    FROM WORK.T1 A
        LEFT JOIN WORK.T2 B on (a.BD=b.BD and a.type=b.type and a.status=b.status);
    QUIT;

    %end;

%end;
%mend;

%inter(start="00:00:00"t, end="01:00:00"t);

Create Table work fine but i can't succeed to insert following data =(

And the log below :

NOTE: Line generated by the invoked macro "INTER".
176           PROC SQL;  INSERT INTO TABLE WORK.NTS_GLOBAL_FINAL (BUSINESS_DATE, SYSENTDEB, SYSENTCRED, TYPE, STATUS,SETTLED,
                                           ____
                                           79
176      ! REMAINING, NUMBER, TS)  SELECT a.BUSINESS_DATE, a.SYSENTDEB, a.SYSENTCRED, a.TYPE, a.STATUS,   (CASE WHEN b.SETTLED = .
176      ! THEN a.SETTLED
ERROR 79-322: Expecting a SELECT.

NOTE: Line generated by the invoked macro "INTER".
176           PROC SQL;  INSERT INTO TABLE WORK.NTS_GLOBAL_FINAL (BUSINESS_DATE, SYSENTDEB, SYSENTCRED, TYPE, STATUS,SETTLED,
                                           ____
                                           202
176      ! REMAINING, NUMBER, TS)  SELECT a.BUSINESS_DATE, a.SYSENTDEB, a.SYSENTCRED, a.TYPE, a.STATUS,   (CASE WHEN b.SETTLED = .
176      ! THEN a.SETTLED
ERROR 202-322: The option or parameter is not recognized and will be ignored.

Thank you for your help.

PS: i'll will ask for a formation on SAS

Re: boucle do with 30 minutes increment

Reality696 — Thu, 06 Apr 2017 14:14:12 GMT

My bad, it's just a syntax error =(