SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Call execute

Reply
Frequent Contributor
Posts: 84

Call execute

I have data sets AAD_1, AAD_2, BBD_1, BBD_2, CCD_1, CCD_2,

and I want to merge AAD_1, BBD_1 and CCD_1  in data set sim_1

and want to merge AAD_2, BBD_2 and CCD_2  in data set sim_2

For this I am using following sas command but there is one error ( I think error space)

data _null_;                                                                                                                                                                                                                                                   

                                                                                                                                                                                                                                                               

     do j=1 to 2;                                                                                                                                                                                                                                              

          call execute(cats(" ",'Data sim_',put(j,1.),';merge AAD_',put(j,1.),'BBD_',put(j,1.),'CCD_',put(j,1.),'; run;'));                                                                                                                                    

    end;

The log error is

NOTE: CALL EXECUTE generated line.

1     + Data sim_1;merge AAD_1BBD_1CCD_1; run;

ERROR: File WORK.AAD_1BBD_1CCD_1.DATA does not exist.

Kindly help in this regard

Super User
Posts: 11,343

Re: Call execute

Before attempting call execute it is often a good idea to test the strings you generate to pass to it. And generally I create the string first and them pass a single string to call execute as it is much easier to debug:

data _null_;

     length tstring $ 200;

     do j= 1 to 2;

          tstring= cats(" ",'Data sim_',put(j,1.),';merge AAD_',put(j,1.),'BBD_',put(j,1.),'CCD_',put(j,1.),'; run;') ;

          put tstring;

     end;

run;

You will find that you generated code similar to:

Data sim_1;merge AAD_1BBD_1CCD_1; run;  NOTE: There is no space between the dataset names.

Frequent Contributor
Posts: 84

Re: Call execute

So how could I add space in merge command? can you specify please

Super User
Super User
Posts: 7,083

Re: Call execute

Just put the spaces into the strings you pass to CALL EXECUTE() and they will be emitted as the code to run.

data _null_;

do j=1 to 2;

   call execute(cats('DATA SIM_',j) ||';merge') ;

   do prefix = 'AAD_','BBD_','CCD_' ;

     call execute(' ' || cats(prefix,j));

  end;

  call execute(';run;');

end;

run;

Frequent Contributor
Posts: 84

Re: Call execute

thank it works fine

One thing I want to know whether call execute allow some calculation or not that is I want  MIBC= 0.5*(A)  +B+ C -(6/2) for both data sets

So I am doing this

data _null_;                                                                                                                                                                                                                               do j=1 to 2;                                                                                                                                                                                                                                 call execute(cats('DATA SIM_',j) ||';merge') ;                                                                                                                                                                               do prefix = 'AAD_','BBD_','CCD_' ;                                                                                                                                                                                              call execute(' ' || cats(prefix,j));                                                                                                                                                                                                 end;                                                                                                                                                                                                                                           call execute('MIBC= 0.5*(A)  +B+ C -(6/2);');

  call execute(';run;');                                                                                                                                                                                                                   end;                                                                                                                                                                                                                                         run;   


But gives some error


NOTE: Line generated by the CALL EXECUTE routine.

11    + MIBC= 5*(A)  +B+ C -(6/2);

            -

            22

            76

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :,

              ;, END, _DATA_, _LAST_, _NULL_.

ERROR 76-322: Syntax error, statement will be ignored.

Super User
Super User
Posts: 7,083

Re: Call execute

Most likely you forgot to include the semicolon to end the MERGE statement before you added the assignment statement.  So it should complain that MIBC= is not a valid option on a MERGE statement.

Frequent Contributor
Posts: 84

Re: Call execute

Ok I am using following code but still get error

data _null_;

do j=1 to 2;                                                                                                                                            call execute(cats('DATA SIM_',j) ||';merge') ;

  do prefix = 'AAD_','BBD_','CCD_' ;

     call execute(' ' || cats(prefix,j));

  end;

  call execute(cats(MIBC = 5*(A)  +B+ C -(6/2)));

  call execute(';run;');

  end;

run;


                     error on log



NOTE: Variable A is uninitialized.

NOTE: Variable B is uninitialized.

NOTE: Variable C is uninitialized.

NOTE: Variable MIBC is uninitialized.

NOTE: Missing values were generated as a result of performing an operation on missing values.

      Each place is given by: (Number of times) at (Line)Smiley SadColumn).

      2 at -38258:-41   2 at -38258:-37   2 at -38258:-35   2 at -38258:-29

NOTE: DATA statement used (Total process time):

      real time           0.02 seconds

      cpu time            0.01 seconds

NOTE: CALL EXECUTE generated line.

1     + DATA SIM_1;merge

2     +  AAD_1

3     +  BBD_1

4     +  CCD_1

NOTE: Line generated by the CALL EXECUTE routine.

5     + 1

        -

        22

        200

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :,

              ;, END, _DATA_, _LAST_, _NULL_.



                                                                                                                                                                                                                                                          

                                                                                                                                                                                                                                                               

Super User
Posts: 11,343

Re: Call execute

This line:

call execute(cats(MIBC = 5*(A)  +B+ C -(6/2)));

is missing quotes to create text for the created syntax and likely only need

call execute("; MIBC = 5*(A)  +B+ C -(6/2);");

Frequent Contributor
Posts: 84

Re: Call execute

Ok thank you now it work fine

I have an other query

I have data sets like

CD_11  CD_12 ........CD_110

CD_21  CD_22.........CD_210

................................................

CD_111  CD_112.........CD_1110

Now I want merge data sets CD_11 CD_12  CD_13 ..... CD_110  into data set CCD_1

similarly CD_21 CD_22  CD_23..... CD_210  into data set CCD_2

..............................................................................

and CD_111 CD_112  CD_113 ..... CD_1110  into data set CCD_11

For this I am using following sas command

data _null_;                                                                                                                                                                                                                                    do j=1 to 11;                                                                                                                                                                                                                                   call execute(cats('data CCD_',put(j,8.),'; set CD_',put(j,8.),'1:CD_',put(j,8.),'10; rename COL1=A;' ));                                                                                            end;                                                                                                                                                                                                                                      run;

But it is not producing desired output

Pls help

Super User
Posts: 19,878

Re: Call execute

Please mark this question answered and post a new question instead of continuing this thread.

Ask a Question
Discussion stats
  • 9 replies
  • 567 views
  • 0 likes
  • 4 in conversation