BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Reality696
Calcite | Level 5

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 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
rogerjdeangelis
Barite | Level 11
%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

...


View solution in original post

8 REPLIES 8
Madansas7b
Obsidian | Level 7

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;

rogerjdeangelis
Barite | Level 11
%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

...


rogerjdeangelis
Barite | Level 11
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;

ballardw
Super User

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.

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

Thank you for the replies guys, it help me 🙂

Reality696
Calcite | Level 5

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

Reality696
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 8 replies
  • 1304 views
  • 0 likes
  • 4 in conversation