topic Re: Create a macro variable based on another dataset in SAS Programming

Create a macro variable based on another dataset

sandyming — Fri, 03 May 2013 02:11:36 GMT

I would like to use estimate value to create a macro variable aa when the wk in data2 matches the wk2 in data1.

The where condition in PROC SQL does not work properly.

Any way to solve this? Thanks.

data data1;

input wk2 estimate;

datalines;

1 12

2 14

3 15

;

run;

%macro trash(var1=, var2=);

%global &var2;

proc sql;

select distinct estimate into: &var2

from summ

where wk2=&var1;

quit;

%mend trash;

data data2;

wk=2;

%trash(var1=wk,var2=aa);

%put &aa;

run;

proc print data=ori;run;

Re: Create a macro variable based on another dataset

Ksharp — Fri, 03 May 2013 04:33:01 GMT

use Call execute() .

Re: Create a macro variable based on another dataset

sandyming — Fri, 03 May 2013 14:32:12 GMT

works. Any other methods to do this?

Re: Create a macro variable based on another dataset

Sudhakar_A — Tue, 07 May 2013 07:36:38 GMT

data data1;

input wk2 estimate;

datalines;

1 12

2 14

3 15

;

run;

data j;

set data1;

if wk2=2 then call symputx("aa", estimate);

run;

Automatically Macro variables created in Call symputx will be global. if you use this out of %macro %mend;

Re: Create a macro variable based on another dataset

UrvishShah — Tue, 07 May 2013 09:19:43 GMT

I think it's working by using WHERE clause in proc sql

proc sql noprint;

select estimate into :aa

from data1 as a,

data2 as b,

where a.wk2 = b.wk;

quit;

%put &aa.;

-Urvish

Re: Create a macro variable based on another dataset

LinusH — Tue, 07 May 2013 10:44:38 GMT

From your example, it seems unnecessary to create data2:

data _null_

set data1;

where ws = '2';

call symput('AA',estimate);

run;

Is ws unique in data1?

What do you intend to use &AA for?

Re: Create a macro variable based on another dataset

Peter_C — Tue, 07 May 2013 20:52:51 GMT

Do you expect process to execute the way it was laid out with indentation - with the sql macro executing within the data step?

A feature being introduced with SAS9.4 (next month) will allow something like that. However you are not using the relevant syntax.

Check out the function DOSUBL() at support.sas.com

peterC

Re: Create a macro variable based on another dataset

siliveri4uall — Fri, 10 May 2013 03:51:19 GMT

Hi,

data data1;

input wk2 estimate;

datalines;

1 12

2 14

3 15

;

run;

%macro Test (num=);

proc sql noprint;

select estimate into : aa from data1

where wk2=&num;

quit;

%put &aa;

data data2;

wk=&num;

estimate=&aa;

run;

proc print;run;

%mend Test;

/* calling the macros */

%test(num=2);

%test(num=3);