BookmarkSubscribeRSS Feed
sandyming
Calcite | Level 5

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;

7 REPLIES 7
sandyming
Calcite | Level 5

works. Any other methods to do this?

LinusH
Tourmaline | Level 20

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?

Data never sleeps
Sudhakar_A
Calcite | Level 5

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;



UrvishShah
Fluorite | Level 6

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

Peter_C
Rhodochrosite | Level 12

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

siliveri4uall
Calcite | Level 5

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=#

quit;

%put &aa;

data data2;

wk=#

estimate=&aa;

run;

proc print;run;

%mend Test;

/* calling the macros */

%test(num=2);

%test(num=3);

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1120 views
  • 3 likes
  • 7 in conversation