topic Re: Call execute in SAS Data Management

Call execute

Rasheed — Wed, 26 Aug 2015 15:24:26 GMT

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

Re: Call execute

ballardw — Wed, 26 Aug 2015 15:40:16 GMT

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.

Re: Call execute

Rasheed — Wed, 26 Aug 2015 15:45:56 GMT

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

Re: Call execute

Tom — Wed, 26 Aug 2015 16:10:27 GMT

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;

Re: Call execute

Rasheed — Wed, 26 Aug 2015 16:44:33 GMT

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

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.

Re: Call execute

Tom — Wed, 26 Aug 2015 16:51:59 GMT

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.

Re: Call execute

Rasheed — Wed, 26 Aug 2015 17:02:59 GMT

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

200

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

;, END, _DATA_, _LAST_, _NULL_.

Re: Call execute

ballardw — Wed, 26 Aug 2015 17:33:17 GMT

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

Re: Call execute

Rasheed — Fri, 28 Aug 2015 16:21:36 GMT

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

Re: Call execute

Reeza — Fri, 28 Aug 2015 16:26:51 GMT

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