topic Re: Create Macro that runs freq if character, means if numeric in SAS Programming

Create Macro that runs freq if character, means if numeric

Ian_Tfirn — Mon, 15 Jul 2019 19:32:38 GMT

Hello all,

As the subject says, I'd like to create a macro that does the following:

1. Identifies a specific variable

2. determines if that variable is character or numeric

3. If it's character, runs proc freq. If it's numeric, runs proc means

I've been attempting various macros that do this but none of have been remotely successful. Any ideas are greatly appreciated.

Thanks

Re: Create Macro that runs freq if character, means if numeric

Reeza — Mon, 15 Jul 2019 19:35:41 GMT

Post what you've tried so far please.

This macro does some of that, except you need to specify what is continuous and what is categorical and binary. The reason I designed it this way is that a numeric variable could still be categorical so using user defined rules seems easier.

https://gist.github.com/statgeek/2f27939fd72d1dd7d8c8669cd39d7e67

@Ian_Tfirn wrote:

Hello all,

As the subject says, I'd like to create a macro that does the following:

1. Identifies a specific variable

2. determines if that variable is character or numeric

3. If it's character, runs proc freq. If it's numeric, runs proc means

I've been attempting various macros that do this but none of have been remotely successful. Any ideas are greatly appreciated.

Thanks

Re: Create Macro that runs freq if character, means if numeric

Reeza — Mon, 15 Jul 2019 19:37:02 GMT

%macro analyze(dataset);



proc means data=&dataset;

var _numeric_;

run;



proc freq data=&dataset;

table _character_;

run;



%mend;



%analyze(sashelp.class);

And that would be a very basic approach to this as well.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Re: Create Macro that runs freq if character, means if numeric

Ian_Tfirn — Mon, 15 Jul 2019 19:40:11 GMT

The general idea I've tried is the following:

proc contents data = original output out = new; run;

%macro Freq_means (var = BMI);

data new;

set new;

if Name = "&Var" and Type = 1 then do;

proc means data = original;

var &var;

run;

If Name = "&Var" and Type = 2 then do;

proc freq data = original;

tables &var;

run;

%mend;

%Freq_means ();

Re: Create Macro that runs freq if character, means if numeric

Reeza — Mon, 15 Jul 2019 19:45:11 GMT

Ok, I've posted a working version of your attempt above, and a much cleaner one above that. I doubt it's super efficient but it's written and works 🙂

Re: Create Macro that runs freq if character, means if numeric

Ian_Tfirn — Mon, 15 Jul 2019 19:47:03 GMT

Hey Reeza,

Unfortunately, your most recent suggestion will run proc means on all numeric variables and proc freq on all character variables. In datasets with hundreds of variables, that's a lot of results!

I only want the macro to identify a single variable, determine if it is character or numeric, and then run the appropriate procedure.

Re: Create Macro that runs freq if character, means if numeric

Reeza — Mon, 15 Jul 2019 19:55:27 GMT

1. SASHELP.VCOLUMN has all variable types, use PROC SQL to get variable types

2. Write macro conditions to execute as necessary.

There are various ways to do #1, proc contents is another, using functions is another option.

%macro analyze_custom(libname=, dset=, var=);

/***insert step 1****/
proc sql noprint;
select type into :vtype where libname = upcase("&libname") and memname = upcase("&dset") and upcase(name) = upcase("&var");
quit;

%if vtype = N %then %do;

/*Insert proc means*/

%end;

%else %do;

/*insert proc freq*/

%end;


%mend;

%analyze_custom(libname=sashelp, dset=class, var=sex);
%analyze_custom(libname=sashelp, dset=class, var=age);

@Ian_Tfirn wrote:

Hey Reeza,

Unfortunately, your most recent suggestion will run proc means on all numeric variables and proc freq on all character variables. In datasets with hundreds of variables, that's a lot of results!

I only want the macro to identify a single variable, determine if it is character or numeric, and then run the appropriate procedure.

Re: Create Macro that runs freq if character, means if numeric

heffo — Mon, 15 Jul 2019 20:14:54 GMT

I like to use sashelp.vcolumn! 🙂

%macro doAnalysis(libname,tablename,variable);
	%macro dummy(); %mend dummy; *Only here to fix syntax highligthing! ;
	proc sql noprint;
		select type into : type  
		from sashelp.vcolumn 
		where 	libname = upcase("&libname") and 
				memname = upcase("&tablename") and
				upcase(name) = upcase("&variable");
	quit;
	%let type =&type; *Get rid of extra spaces;

	%if "&type" = "char" %then %do;
		proc freq data=&libname..&tablename;
			table &variable;
		run;
	%end;
	%if "&type" = "num" %then %do;
		proc means data=&libname..&tablename;
			var &variable;
		run;
	%end;
%mend doAnalysis;

%doAnalysis(sashelp, class, sex);
%doAnalysis(sashelp, class, age);

Re: Create Macro that runs freq if character, means if numeric

Ian_Tfirn — Mon, 15 Jul 2019 20:40:45 GMT

Re: Create Macro that runs freq if character, means if numeric

Reeza — Mon, 15 Jul 2019 20:39:41 GMT

Post the full code you ran.

Re: Create Macro that runs freq if character, means if numeric

Ian_Tfirn — Mon, 15 Jul 2019 20:42:33 GMT

Works perfectly, thanks for your help!

Re: Create Macro that runs freq if character, means if numeric

Ian_Tfirn — Mon, 15 Jul 2019 20:44:13 GMT

Hey Reeza,

For some reason, the macro in your most recent comment wasn't working for me while Heffo's did. Since they are nearly identical, I'm sure I was doing something wrong.

Anyway, thanks for your patience. I appreciate you sticking with me.

Re: Create Macro that runs freq if character, means if numeric

Reeza — Mon, 15 Jul 2019 20:46:51 GMT

I forgot type returns num/char not N/C but that's an easy fix if you compare the two.

Re: Create Macro that runs freq if character, means if numeric

Reeza — Mon, 15 Jul 2019 20:48:30 GMT

@heffo, use the TRIMMED option in PROC SQL to avoid the need to get rid of extra spaces issue. You should also remove the quotes from the text comparison.

Re: Create Macro that runs freq if character, means if numeric

Ian_Tfirn — Mon, 15 Jul 2019 20:55:42 GMT

Even when I change N to num, it still runs proc freq for my numeric var

Re: Create Macro that runs freq if character, means if numeric

Reeza — Mon, 15 Jul 2019 21:01:43 GMT

add a & before vtype.

Re: Create Macro that runs freq if character, means if numeric

Ian_Tfirn — Mon, 15 Jul 2019 21:05:13 GMT

Works perfect now. Thanks for all your help!

Re: Create Macro that runs freq if character, means if numeric

ballardw — Mon, 15 Jul 2019 22:06:02 GMT

I would also suggest using DICTIONARY.COLUMNS in stead of SASHELP.VCOLUMN in Proc SQL. I notice very different performance, seconds versus minutes, with the number of libraries, datasets and variables involved on my system.

Since the OP says that his datasets have "hundreds of variables" this performance might be noticeable.

Re: Create Macro that runs freq if character, means if numeric

heffo — Mon, 15 Jul 2019 22:40:20 GMT

At one time or another I had some problems with missing values and quotes. So, I started to use the quotes to help this. I don't know if this is the best way to do it.
It might have been when having x ne &y or similar and not the = .

Re: Create Macro that runs freq if character, means if numeric

Reeza — Tue, 16 Jul 2019 03:58:47 GMT

The difference matters with the number of data sets and libraries, not columns. If that was the case I'd switch to use PROC CONTENTS or FUNCTIONS. The difference between dictionary and sashelp doesn't exist anymore the last time I tested it.