BookmarkSubscribeRSS Feed
Rasheed
Calcite | Level 5

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

9 REPLIES 9
ballardw
Super User

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.

Rasheed
Calcite | Level 5

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

Tom
Super User Tom
Super User

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;

Rasheed
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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.

Rasheed
Calcite | Level 5

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):(Column).

      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_.



                                                                                                                                                                                                                                                          

                                                                                                                                                                                                                                                               

ballardw
Super User

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);");

Rasheed
Calcite | Level 5

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

Reeza
Super User

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

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 1456 views
  • 0 likes
  • 4 in conversation