topic Re: Create a Variable from dataset in Statistical Procedures

Create a Variable from dataset

mohamedk — Sat, 03 Apr 2021 20:59:32 GMT

Hi All

I’d like to create a variable or macro that includes info from an existing dataset.

What I’m trying to achieve is to add a where clause to look at the info without having to write each one down all the time.

Eg:
Data want;
Set have;
Where column in (column1);
Run;

Re: Create a Variable from dataset

PaigeMiller — Sat, 03 Apr 2021 21:48:41 GMT

@mohamedk wrote:

I’d like to create a variable or macro that includes info from an existing dataset.

What I’m trying to achieve is to add a where clause to look at the info without having to write each one down all the time.

Eg:
Data want;
Set have;
Where column in (column1);
Run;

I'm afraid I don't understand

Where column in (column1);

as this is not valid syntax in neither a DATA step nor in PROC SQL, and in my mind not valid logic here either. What is the logic you are trying to implement? Can you give simple examples?

Re: Create a Variable from dataset

ballardw — Sat, 03 Apr 2021 22:42:03 GMT

@mohamedk wrote:
Hi All

I’d like to create a variable or macro that includes info from an existing dataset.

What I’m trying to achieve is to add a where clause to look at the info without having to write each one down all the time.

Eg:
Data want;
Set have;
Where column in (column1);
Run;

Your "request" sounds like you want all the values for a variable. At which point a "where" is useless as by default you would get all the values.

So you should provide a mite more detailed description such as example data, rules for selecting values from that data set and then we might have something that makes sense.

Re: Create a Variable from dataset

Tom — Sun, 04 Apr 2021 16:27:02 GMT

For this to make sense you need to have two datasets. One with the list of values your want to filter to. For example a list of subjects or list of states etc. The other is the dataset you want to analyze.

So you could just merge the two. If they are sorted already by that variable use a simple data step merge.

data want;
  merge have list(in=inlist);
  by subject ;
  if inlist;
run;

Or you could use SQL instead.

proc sql ;
create table want as
  select * 
  from have
  where subject in (select subject from list)
;
quit;

If the list of values is smallish you could put them into a macro variable.

proc sql noprint;
select distinct subject into :slist separated by ' '
from list
;
quit;

Note if the variable is character add quotes.

proc sql noprint;
select distinct quote(trim(subject)) into :slist separated by ' '
from list
;
quit;

Then use the list in your WHERE clause.

data want ;
  set have;
  where subject in (&slist);
run;

Re: Create a Variable from dataset

mohamedk — Sun, 04 Apr 2021 21:24:23 GMT

Apologies All,

It seems I was quite unclear in my questions.

My current query is:

proc sql;
create table test as 
select 
a.*,
b.field1,
b.field2,
b.field3
from tableA as a
LEFT JOIN 
	tableB as b ON a.CustomerNum = b.CustomerNum
where 
	b.field1 IN (123,456,789,010,111,212,131,415);
quit;

I'd like to change the way I'm manually adding the numbers in the where clause to something less manual as currently I copied and pasted to test the logic of my query.

Hoping the above makes more sense that my initial query

cc:@PaigeMiller @ballardw

Re: Create a Variable from dataset

mkeintz — Mon, 05 Apr 2021 03:04:02 GMT

And where did you get the values that you manually entered in the list

(123,456,789,010,111,212,131,415)

To "de-manual" this process of adding to this list, you need to tell us how these numeric values were established.

Re: Create a Variable from dataset

mohamedk — Mon, 05 Apr 2021 06:42:17 GMT

From a previous output

Re: Create a Variable from dataset

Kurt_Bremser — Mon, 05 Apr 2021 08:48:17 GMT

By "output", do you mean "results" from some kind of reporting procedure, or a dataset created in a previous step?

Re: Create a Variable from dataset

PaigeMiller — Mon, 05 Apr 2021 10:26:24 GMT

@mohamedk wrote:
From a previous output

So somehow, these numbers were in a previous output. What you ought to do is have the previous output store the numbers in a data set. Then, you can do something like this, where the variable name in the previous data set is ABC:

LEFT JOIN 
	tableB as b ON a.CustomerNum = b.CustomerNum
where 
	b.field1 IN (select distinct abc from mypreviousdataset);
quit;

By the way, when you need help with SQL, don't start the thread by asking a DATA step question.

Re: Create a Variable from dataset

mohamedk — Mon, 05 Apr 2021 20:51:01 GMT

Thank you. Will try this out.

Apologies, relatively new to SAS so will post correctly In future.

Edit: @PaigeMiller Thank you - your suggestion worked.

Re: Create a Variable from dataset

Kurt_Bremser — Tue, 06 Apr 2021 04:57:49 GMT

You can also use a hash for a lookup:

data want;
set have;
if _n_ = 1
then do;
  declare hash l (dataset:"previous (keep=field1)");
  l.definekey(field1);
  l.definedone();
end;
if l.check() = 0;
run;