Creating macro variables in an SQL and pass back

[Patrick]

Hi Thomas
Could it be that you're just after that?

%MACRO start(Zahl);
proc sql;
select ID, VERSION into :ID, :VERSION
from myLib.mytable
where LOOK_HERE = &Zahl. AND LOOK_THERE = 'T';
quit;

data test_port;
test_601_ID = &ID.;
test_601_VERSION = &VERSION.;
run;

proc print data = test_port;
run;

%MEND start;

%start(601)


BUT - Then one would may be write it this way:

%MACRO start(Zahl);
proc sql;
select ID as test_&Zahl._ID, VERSION as test_&Zahl._Version
from myLib.mytable
where LOOK_HERE = &Zahl. AND LOOK_THERE = 'T';
quit;
%MEND start;

%start(601)


HTH
Patrick

[ThomasH]

Hi,

thanks for your replies - I have changed the code to run the SQL first, which did the job.

However, this works only in case the values I am looking up with the SQL are fixed.

How would that work in case I have an undefined set of variables to look up?

E.g. (pseudo code)

macro test(value)
proc sql;
select x into :var_x from y where z = value;
quit;
mend test;

data someData;
set anotherDataset;
for each row
get the value of column3 and call the proc sql step with the value of column3;
end;
another_column = &var_x;
run;

Hope that gives a picture of what I was thinking about.

Thanks and cheers,
Thomas


Message was edited by: ThomasH Message was edited by: ThomasH

[LinusH]

Why don't you just join the two tables:

proc sql;

create table SomeData as
select x as another_column
from anotherDataset left join y
on anotherDataset.column3 eq y.z

quit;

Linus