Proc freq does not take sql macro variable

Reply
Frequent Contributor
Posts: 90

Proc freq does not take sql macro variable

[ Edited ]

I am trying to make proc freq understand sql macro variable. The code looks like this:

 

%let libname = DM_CMDM;

/*[...]*/
/*this bit of code is working*/ proc sql noprint; select distinct table_name into: var1 - : var&nrows from tables; quit; %macro loop_(); %do i = 1 %to &nrows; /*working bit of code*/ proc contents data=&libname..&&var&i Position noprint out=content_&&var&i; run; /*this bit of code is not working due to data=&libname..&&var&i cannot be read properly*/ ods table onewayfreqs=temp_&&var&i;
/*this bit of code is working*/
proc format;
value $ missfmt ' ' = "Missing" other = "Not_Missing";
value nmissfmt . = "Missing" other = "Not_Missing";
run;
proc freq data=&libname..&&var&i; /*this causes problems*/
table _all_/missing;
format _numeric_ nmissfmt. _character_ $missfmt.;
run; %end; %mend; %loop_()

I cannot understand why "proc contents" works and understands &libname..&&var&i and "proc freqs" does not. Any suggestion how to amend the script so that it worked?

 

Sorry for the typos, I cannot copy/paste between remote and this desktop. The problem is actually where I stated. If write the table name by hand, it works flawleslly.

 

Thanks!

Super Contributor
Posts: 349

Re: Proc freq does not take sql macro variable

Posted in reply to Uknown_user

Maybe the missing colon after

ods table onewayfreqs=temp_&&var&i

causes the problem. Posting log with options mprint, mlogic and symbolgen active will help in the bug-hunt.

Super User
Super User
Posts: 8,152

Re: Proc freq does not take sql macro variable

Posted in reply to Uknown_user

What is it your trying to do?  Start by doing something using Base SAS, I keep repeating this, Base SAS is the programming language, macro is not a replacement for this.  There are at least two far simpler options which jump to mind just from that code:

A) Put your data together, it is rarely a good idea to keep same data in different datasets - it leads to this kind of messy hard to maintain coding:

data total;
  set ... indsname=tmp;
  file=tmp;
run;

As I don't know your data I can't specify the set.   This combines data and creates a new column for the table name.  You can then write very simple and easy to use code:

proc freq data=total;
  by file;
  ...
run;

No macro, just simple inbuilt by group processing - just by using this thinking all of your code will dilute down to 10% of the orignal coding and be far easier to use.

 

B)  You could also avoid the loop by using an inherent loop in the dataset:

data _null_;
  set tables;
  call execute("proc freq data=&library.."||strip(table_name)||"; ...; run;");
run;
Frequent Contributor
Posts: 90

Re: Proc freq does not take sql macro variable

Thanks for your reply. I REALLY do not want to work with one big data set. What I am trying to do is to take all tables from one library and run a script on them one by one. The reason for that is execution in waves (i.e. in one wave 10 tables or something like that).

 

Please note that there are tables with 1 bil+ rows which will be excluded easily when using macro variable with table names.

 

Having said that, I need to choose some other form of &&var&i to make the proc freq understand what table it is. Thanks.

Trusted Advisor
Posts: 1,613

Re: Proc freq does not take sql macro variable

[ Edited ]
Posted in reply to Uknown_user

There is no problem with PROC FREQ.

Run next simplified code - it works fine:

%let libname = sashelp;
%let i = 2;
%let var2 = class;

proc freq data=&libname..&&var&i;
   table sex;
run;

Your issue is missing semicolon ( ; ) at the end od ODS TABLE statement,

as @andreas_lds mentiond.

Frequent Contributor
Posts: 90

Re: Proc freq does not take sql macro variable

[ Edited ]

Hi, I cannot copy/paste the text of the script so I simply rewrote it and missed a ";" in here but in the original script it is present there. That is not the problem - as I mentioned if I write the libname and table name by hand, it works, even this works: &libname..TABLE (where TABLE is the actual name of table written by hand). Thanks.

Super User
Super User
Posts: 8,152

Re: Proc freq does not take sql macro variable

Posted in reply to Uknown_user

If you don't want one big dataset, then use option B.

Frequent Contributor
Posts: 90

Re: Proc freq does not take sql macro variable

I do not think that option B can be used either. I tried to rewrite it as follows: data tmp; set &libname..&&var&i; call execute("proc freq data=&libname.."strip("&&var&i.")||"; table _all_ / missing; format _numeric_ nmissfmt. _character_ $missfmt.; run;"); run; This produced empty output with just headers and no records in it...

Super User
Super User
Posts: 8,152

Re: Proc freq does not take sql macro variable

Posted in reply to Uknown_user

Please re-read the code.  From your first post you have a dataset called table_names which contains, I assume, the tables you want to proc freq.  This is the basis for the data _null_ step:

data _null_;
  set tables;   /* For each row in dataset tables generate the below code */
  call execute("proc freq data=&library.."||strip(table_name)||"; ...; run;");
/* Use table_name from tables dataset as the generic part of the code */ run;  

 Do bear in mind that I am writing code completely blind here as I have no idea what your data looks like.

Trusted Advisor
Posts: 1,058

Re: Proc freq does not take sql macro variable

Posted in reply to Uknown_user

Perhaps you don't want a big data set FILE, but  maybe a data set VIEW would serve your needs, without adding to disk space requirements, or extra processing time:

 

 

data total /view=total;
  set ... indsname=tmp;
  file=tmp;
run;

 

You can then use a "BY FILE" statement  as per @RW9's suggestion.

Frequent Contributor
Posts: 90

Re: Proc freq does not take sql macro variable

I appreciate all of your suggestions. This in fact might be more efficient, however, I would have to rewrite the whole piece of code to make it work (there are further steps which I did not post thought) and none of these posts has actually answered the question. I simply need to the proc freq understand &&var&i and do not know how to do that. Thanks

Super User
Super User
Posts: 8,152

Re: Proc freq does not take sql macro variable

Posted in reply to Uknown_user

You need to post the log, we cannot guess what the run looks like - just the bit around the proc freq, including any let statements etc.

Super User
Posts: 8,037

Re: Proc freq does not take sql macro variable

Posted in reply to Uknown_user

Insert %put statements at proper places to get a feel what your macro does; also use options mprint mlogic symbolgen;

 

Other posts have already given evidence that proc freq will "understand" an indirect macro variable resolution.

(actually, it's not proc freq, it's just the macro preprocessor resolving macro variables)

 

Run this:

%let libname = WORK;

data work.table1;
set sashelp.class;
run;

data work.table2;
set sashelp.class;
run;

data tables;
input table_name $;
cards;
table1
table2
;
run;

%let nrows=2;

proc sql noprint;
select distinct table_name
into: var1 - : var&nrows
from tables;
quit;


%macro loop_();

%do i = 1 %to &nrows;

/*working bit of code*/
proc contents data=&libname..&&var&i Position noprint
out=content_&&var&i;
run;

/*this bit of code is not working due to data=&libname..&&var&i cannot be read properly*/

ods table onewayfreqs=temp_&&var&i;

/*this bit of code is working*/
proc format;
value $ missfmt ' ' = "Missing" other = "Not_Missing";
value nmissfmt . = "Missing" other = "Not_Missing";
run;

proc freq data=&libname..&&var&i; /*this causes problems*/
table _all_/missing;
format _numeric_ nmissfmt. _character_ $missfmt.;
run;

%end;

%mend;

%loop_()

and you will see that the core of your macro as posted DOES work, and that there is NO problem with

&libname..&&var&i

in the proc freq. So your code as posted has to have essential differences to the code you really run.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 90

Re: Proc freq does not take sql macro variable

Posted in reply to KurtBremser

I might have found the cause of this problem. In the library are two objects with same name: mt=view and mt=data. As the object with mt=data is empty in given enviroment, the mt=view is not. I would need to make the program understand that it needs to execute proc freq on mt=view - is there any way how to proceed? Thanks

Super User
Posts: 8,037

Re: Proc freq does not take sql macro variable

Posted in reply to Uknown_user

Uknown_user wrote:

I might have found the cause of this problem. In the library are two objects with same name: mt=view and mt=data. As the object with mt=data is empty in given enviroment, the mt=view is not. I would need to make the program understand that it needs to execute proc freq on mt=view - is there any way how to proceed? Thanks


Basically, that's an invalid state. SAS does not allow a view and a dataset of the same name in the same library at any given time.

This program will fail when attempting to create the views:

data work.table1;
set sashelp.class;
run;

data work.table2;
set sashelp.class;
run;

data work.table1/view=work.table1;
set sashelp.class;
run;

data work.table2/view=work.table2;
set sashelp.class;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 18 replies
  • 180 views
  • 5 likes
  • 7 in conversation