BookmarkSubscribeRSS Feed
Fisher
Quartz | Level 8

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

13 REPLIES 13
Tom
Super User Tom
Super User

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;

Ron_MacroMaven
Lapis Lazuli | Level 10

yeah, sql is more simple

... select distinct ...

...

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

PGStats
Opal | Level 21

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
Tom
Super User Tom
Super User

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.

PGStats
Opal | Level 21

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

PG
Ron_MacroMaven
Lapis Lazuli | Level 10

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;

Ksharp
Super User

I would recommend to use Hash Table.

Ron_MacroMaven
Lapis Lazuli | Level 10

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

Fisher
Quartz | Level 8

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.

Reeza
Super User

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.

Fisher
Quartz | Level 8

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

Tom
Super User Tom
Super User

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.

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
  • 13 replies
  • 3814 views
  • 5 likes
  • 6 in conversation