Count with left

[forumsguy]

Hi All,

I am trying to create macro variables using proc sql within a macro.But the code does not work. its pretty simple code but I do not understand why its not working.

data have;

input var;

datalines;

12

34

8

79

84

;

run;

%macro m1;

proc sql noprint;

select count(var) into :cnt from have ;

quit;

Proc sql noprint;

select var into :val1 - :val%left(&cnt) from have;

Quit;

%mend;

%m1;

%put &val1 &val2 &val3 &val4 &val5;

[Haikuo · Accepted solution]

There are two ways to make the macro variables global:

1) Remove macro definition completely.

2) Using %global statement

Otherwise they are local, they still can be printed out within your macro though.

Haikuo

[manojinpec]

what is the error your getting? I think you cannot use %left function . What you can do %let cnt=%trim(&cnt) before the second query.

[LinusH]

There is no problem with the %left. This is as Haikuo already stated a problem with local vs global macro variables.
Removing the macro definition seems to the most reasonable action, having the code wrapped up inside a macro does not add anything...

[kuridisanjeev]

Yes ,You Have to mention %global in your macro as said by .

or else you have to create all the macro variables  with dummy values before the macro.

like

data have;

input var;

datalines;

12

34

8

79

84

;

run;

%let Val1=0;

%let Val2=0;

%let Val3=0;

%let Val4=0;

%let Val5=0;

%macro m1;

proc sql noprint;

select count(var) into :cnt from have ;

quit;

Proc sql noprint;

select var into :val1 - :val%left(&cnt) from have;

Quit;

%mend;

%m1;

%put &val1 &val2 &val3 &val4 &val5;

But i always prefer to use  %Global.

Regards.

Sanjeev.K