topic Re: create character macro variable in SAS Programming

create character macro variable

VanDalucas — Wed, 15 May 2019 16:21:50 GMT

Hi guy,

I am trying to create a macro variable using the following statement:

proc sql;
select max(par_dt) into :var from ookla.ookla_all_stats;
run;

the point is the "var" variable is always of numeric type even through par_dt is character.

(I need to use the 'var' variable in a where statement of a proc imstat where no data type conversion can take place )

Does anybody have an idea of how to do that ?

thanks

Re: create character macro variable

PaigeMiller — Wed, 15 May 2019 16:36:24 GMT

There's no such thing in SAS as a numeric macro variable. They are all character. Even if they look to us humans as a number, SAS considers it to be character.

But anyway, your description of the problem needs a lot more details before we can provide any meaningful advice

Re: create character macro variable

Tom — Wed, 15 May 2019 16:42:44 GMT

Macro variables always contain text. The text might look like numbers and if you use them in a place where SAS expects to see a number then when expanded they will be treated by SAS as a number.

If your PAR_DT variable is a valid date (or datetime) value then your code will pull out the unformatted number. You can then use this number in your code to compare to the values of PAR_DT variable (or any other number).

Note since you didn't tell PROC SQL how to format the max value it will use BEST8. , which can be too short for raw datetime values (number of seconds since 1960). So instead of getting a value like 1873543197 you will get 1.8735E9. Just tell SAS to use a longer format. You probably don't have fractions of a second so just use the 32. format.

proc sql noprint;
%let var=.;
select max(par_dt) format=32.
  into :var trimmed 
from ookla.ookla_all_stats
;
quit;

data want;
  set ookla.ookla_all_stats;
  where par_dt = &var ;
run;

Re: create character macro variable

ballardw — Wed, 15 May 2019 16:44:08 GMT

@VanDalucas wrote:

Hi guy,

I am trying to create a macro variable using the following statement:

proc sql;
select max(par_dt) into :var from ookla.ookla_all_stats;
run;

the point is the "var" variable is always of numeric type even through par_dt is character.

(I need to use the 'var' variable in a where statement of a proc imstat where no data type conversion can take place )

Does anybody have an idea of how to do that ?

thanks

Prove that "var" is numeric.

And you want to very closely examine any process involving Max or Min functions involving character values. Max of "9" "1000" ".45" is what?

All macro variables are always text. Some just look like numbers.

If var is a single value then likely you need to do one of two things: in the where clause you reverence the variable place the macro variable inside double quotes:

where myvariable = "&var."

OR place the quotes inside the macro value:

proc sql;
select quote(strip(max(par_dt))) into :var from ookla.ookla_all_stats;
run;

and use as

where myvariable =&var.;

The strip is likely needed to prevent have trailing spaces inside the quotes which would make "Yes" not equal to "Yes ".

Re: create character macro variable

Kurt_Bremser — Wed, 15 May 2019 16:53:39 GMT

Macro variables do not have types, they are always just text. There is no such thing as a numeric macro variable, it can only contain text that looks like a number (and can be used as such, under specific circumstances).

And maximums of character variables can be tricky, as already mentioned.

Re: create character macro variable

VanDalucas — Wed, 15 May 2019 16:56:38 GMT

Guys thanks you all for your posts!

yes, it appears that all I needed to do was to enclose the var in ".."