DATA Step, Macro, Functions and more

Fetch distinct variable values during the macro process

Reply
Contributor
Posts: 70

Fetch distinct variable values during the macro process

Hi,

I like to develop an macro to extract data from a big dataset. In the macro, I need to fetch all distinct values from the big dataset first to validate some parameter values provided by the user. If the parameter value match one of distinct variable values, then the parameter value provided is valid, otherwise write error message in the log or message window to list all distinct values to user. I know I can use %sysfunct(open()), %sysfunc(varnum()), &sysfunc(fetchobs()), etc to open and fetch an obs from it, however, this apparently doesn't meet my requirement as it only fetch one obs each time and it doesn't make sense to loop all dataset to find all distinct values of some var. I wonder there is any way to fetch all distinct values from a dataset during the macro process, I googled it and get nothing really useful.

Thanks

Super User
Super User
Posts: 6,502

Re: Fetch distinct variable values during the macro process

Why not just ask normal SAS procedures to get the values for you?

proc sql noprint ;

  select distinct myvar into :valid_list separated by ' ' from mastertable ;

quit;

Or even have it check if your value is valid.

%let valid=0;

proc sql noprint ;

select 1 into :valid from mastertable where myvar = "&mymacrovar" ;

quit;

Regular Contributor
Posts: 200

Re: Fetch distinct variable values during the macro process

yeah, sql is more simple

... select distinct ...

...

%let Found = %index(&Var,&list);

Respected Advisor
Posts: 4,654

Re: Fetch distinct variable values during the macro process

To get a single value and speed up the query :

%let valid=0;

proc sql noprint outobs=1 ;

select 1 into :valid from mastertable where myvar = "&mymacrovar" ;

quit;

PG

PG
Super User
Super User
Posts: 6,502

Re: Fetch distinct variable values during the macro process

That is not necessary as PROC SQL will stop at the first observation when pulling values into macro variables if you have not included he SEPARATED BY clause.

Respected Advisor
Posts: 4,654

Re: Fetch distinct variable values during the macro process

Couldn't find it in the doc, so, I tested and IT'S TRUE. Thanks, Tom. - PG

PG
Regular Contributor
Posts: 200

Re: Fetch distinct variable values during the macro process

Tom's answer provides a list in a macro variable.

This A provides a data set.

%let value = 13;

%let value = 'I';

%let Lib_Data = Library.MyData;

%Let var = TheVar;

PROC Freq data = &Lib_Data;

   tables &var

   / list missing noprint

   out = work.List;

DATA ExistValue;

retain Found 0;

do until(EndoFile);

    set &Lib_Data

        (where = (&Var eq &Value))

        end = EndoFile

        in = HaveValue;

        if HaveValue then Found=1;

    end;

call sumputX('found',Found);

run;

Super User
Posts: 9,685

Re: Fetch distinct variable values during the macro process

I would recommend to use Hash Table.

Regular Contributor
Posts: 200

Re: Fetch distinct variable values during the macro process

It seems to me that you want to maintain a table of the distinct values of the variable that the user is supposed to choose from.

It seems obvious to assume that you are allowing the user to pick a subset,

which means that they are choosing a value of a classification variable

also known as a foreign key.

The values of a classification variable are unlikely to change

so the dynamic solutions presented

1. sql select distinct

2. proc freq

are unnecessary if the set of choices is static.

summary:

1. get the list of values, once, save it

2. develop a solution which either

2.1 presents user with limited choice

2.2 parses user choice against data set list of values.

Ron Fehd  design maven

Contributor
Posts: 70

Re: Fetch distinct variable values during the macro process

Thanks for your suggestion and time, everyone.

We know, when we call a macro in our SAS program, SAS compiler will forward all macro statement to macro possessor. Macro processor executes all macro statements only and return regular SAS statement or procedure back to SAS compiler. So far, all the solutions you suggested will not processed during the macro process. What I really try to do is to fetch all distinct values DURING MACRO PROCESS. If the parameter value provided by user doesn't match any values in the dataset, then macro stop executing and write error message in log or message window(preferred) and list all legal values.

Super User
Posts: 17,868

Re: Fetch distinct variable values during the macro process

Fisher wrote:

1. So far, all the solutions you suggested will not processed during the macro process.

2. What I really try to do is to fetch all distinct values DURING MACRO PROCESS.

RE 1: Yes it can, you can easily do a proc sql select distinct wrapped into your macro. If that isn't what you want then you're not being clear enough.

RE 2: That is possible but INEFFICIENT. Reading through your entire dataset every time you try to run a macro to generate the variable lists is a waste of time and power. If your dataset is large this will also take a long time in terms of a person waiting for a report to be generated. Ron's suggestion of storing the variables ahead of time, as a lookup or code table is more efficient, and generally better program design.

Contributor
Posts: 70

Re: Fetch distinct variable values during the macro process

Thanks Reeza, you got what I mean.

I am very interested in yor RE1, but how can I wrap a proc sql in a macro? can you give an example?  seems I am closer to the solution. Smiley Happy

Super User
Posts: 17,868

Re: Fetch distinct variable values during the macro process

Super User
Super User
Posts: 6,502

Re: Fetch distinct variable values during the macro process

Why?

If when the parameters passed by the user are valid the macro will generate multiple SAS statements or steps then why can't it issue them during the process of validating the user parameters?

The only reason would be if the intended use of the macro is within part of a step or part of statement.  If you really, really need to do that then create a dataset of view that has the distinct values and loop using the %sysfunc() to call the fetch() function to read the values into macro variables. You can use %syscall set() to link the dataset variables to macro variables that receive the values fetched.

Ask a Question
Discussion stats
  • 13 replies
  • 659 views
  • 5 likes
  • 6 in conversation