topic Re: Proc freq does not take sql macro variable in SAS Programming

Proc freq does not take sql macro variable

Uknown_user — Wed, 25 Oct 2017 10:31:52 GMT

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!

Re: Proc freq does not take sql macro variable

andreas_lds — Wed, 25 Oct 2017 09:39:59 GMT

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.

Re: Proc freq does not take sql macro variable

RW9 — Wed, 25 Oct 2017 09:43:33 GMT

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;

Re: Proc freq does not take sql macro variable

Uknown_user — Wed, 25 Oct 2017 09:50:15 GMT

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.

Re: Proc freq does not take sql macro variable

Shmuel — Wed, 25 Oct 2017 10:31:13 GMT

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.

Re: Proc freq does not take sql macro variable

Uknown_user — Wed, 25 Oct 2017 10:35:46 GMT

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.

Re: Proc freq does not take sql macro variable

RW9 — Wed, 25 Oct 2017 10:37:19 GMT

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

Re: Proc freq does not take sql macro variable

Uknown_user — Wed, 25 Oct 2017 11:14:42 GMT

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...

Re: Proc freq does not take sql macro variable

RW9 — Wed, 25 Oct 2017 11:24:17 GMT

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.

Re: Proc freq does not take sql macro variable

mkeintz — Wed, 25 Oct 2017 11:28:52 GMT

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.

Re: Proc freq does not take sql macro variable

Uknown_user — Wed, 25 Oct 2017 11:57:58 GMT

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

Re: Proc freq does not take sql macro variable

RW9 — Wed, 25 Oct 2017 12:37:57 GMT

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.

Re: Proc freq does not take sql macro variable

Kurt_Bremser — Wed, 25 Oct 2017 12:44:00 GMT

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.

Re: Proc freq does not take sql macro variable

Uknown_user — Wed, 25 Oct 2017 13:01:31 GMT

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

Re: Proc freq does not take sql macro variable

Kurt_Bremser — Wed, 25 Oct 2017 13:14:55 GMT

@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;

Re: Proc freq does not take sql macro variable

Shmuel — Wed, 25 Oct 2017 13:15:41 GMT

You should check why tere are the two objects with same name but different type (data/view).

May be the empty data is a result of a bug or created unintentionally ?

If you are convinced that there must be both types with same name, you need

add to your macro checking the member type and how many observations there are,

but the - which of them is the right to use, if both - the data and the view - have real observations?

Re: Proc freq does not take sql macro variable

Uknown_user — Wed, 25 Oct 2017 13:15:47 GMT

I have managed to make the script understand that it will only be executed for non-empty tables (views in my case). Now the whole piece of code works. Those of you that stated the script is fine were right, I just needed to exclude empty tables from the library and then run it. Thanks

Re: Proc freq does not take sql macro variable

Uknown_user — Wed, 25 Oct 2017 13:17:42 GMT

you are right, there were two chars of the whole string name different but I did not notice that before. In the initial stage I accidentely included empty tables which was consequently fixed.

Re: Proc freq does not take sql macro variable

Tom — Wed, 25 Oct 2017 13:36:08 GMT

Sounds like your problem was not with the macro variable generation and usage, but in the selection of which members to include in the list.

But you can simplify the way that you use SQL to generate the list of macro variables.

You do not need to know in advance the number of variables that it creates. Instead let PROC SQL tell you how many it finds.

Just leave the upper bound unspecified and save the automatic macro variable SQLOBS to see how many rows/macro variables were generated.

proc sql noprint;
select distinct table_name
  into :var1-
  from tables
;
%let nrows=&sqlobs ;
quit;

If you are running a really old version of SAS then you might need add an upper bound, but it just needs to be at least as large as the maximum number of rows you could get. SQL will only create the number of macro variables it actually needs.

proc sql noprint;
select distinct table_name
  into :var1-:var99999
  from tables
;
%let nrows=&sqlobs ;
quit;