BookmarkSubscribeRSS Feed
Uknown_user
Quartz | Level 8

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!

18 REPLIES 18
andreas_lds
Jade | Level 19

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Uknown_user
Quartz | Level 8

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.

Shmuel
Garnet | Level 18

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.

Uknown_user
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Uknown_user
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Uknown_user
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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

 

Uknown_user
Quartz | Level 8

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

Kurt_Bremser
Super 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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 852 views
  • 5 likes
  • 7 in conversation