topic Re: Searching for values across a range of variables in SAS Programming

Searching for values across a range of variables

xezus — Thu, 09 Apr 2020 15:04:25 GMT

Hello,

I am working with a dataset similar to the one shown here just with many more variables and consistently changing variables month to month.

Date	Cats	Dogs	Rabbits	Magic Number
January	8	4	1	10
February	2	6	10	5
March	15	22	5	8
April	20	8	6	12
May	15	4	4	16
June	0	7	11	2
July	4	4	4	4
August	9	7	3	7
September	25	12	9	5
October	6	1	0	10
November	16	10	40	14
December	3	1	8	11

I would like to search the range of variables from Cat to Rabbit to find anything within 2 of the Magic Number. If found I would like it to return the variable name, if not null, or "multiple" if it finds more than one potential match to the Magic Number.

So my desired output would look like

Date	Cats	Dogs	Rabbits	Magic Number	Matching Category
January	8	4	1	10	Cats
February	2	6	10	5	Dogs
March	15	22	5	8	None
April	20	8	6	12	None
May	15	4	4	16	Cats
June	0	7	11	2	Cats
July	4	4	4	4	Multiple
August	9	7	3	7	Multiple
September	25	12	9	5	None
October	6	1	0	10	None
November	16	10	40	14	Cats
December	3	1	11	11	Rabbits

I'd like to avoid the tedious process of hardcoding every potential scenario as I have many more variables to search through for the 'Magic Number' and they can change month to month so I'd like to only have to update the range if any new variables occur.

Thank you for your help in advance

Re: Searching for values across a range of variables

Reeza — Thu, 09 Apr 2020 15:12:48 GMT

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

@xezus wrote:

Hello,

I am working with a dataset similar to the one shown here just with many more variables and consistently changing variables month to month.

Date Cats Dogs Rabbits Magic Number

January 8 4 1 10

February 2 6 10 5

March 15 22 5 8

April 20 8 6 12

May 15 4 4 16

June 0 7 11 2

July 4 4 4 4

August 9 7 3 7

September 25 12 9 5

October 6 1 0 10

November 16 10 40 14

December 3 1 8 11

I would like to search the range of variables from Cat to Rabbit to find anything within 2 of the Magic Number. If found I would like it to return the variable name, if not null, or "multiple" if it finds more than one potential match to the Magic Number.

So my desired output would look like

Date Cats Dogs Rabbits Magic Number Matching Category

January 8 4 1 10 Cats

February 2 6 10 5 Dogs

March 15 22 5 8 None

April 20 8 6 12 None

May 15 4 4 16 Cats

June 0 7 11 2 Cats

July 4 4 4 4 Multiple

August 9 7 3 7 Multiple

September 25 12 9 5 None

October 6 1 0 10 None

November 16 10 40 14 Cats

December 3 1 11 11 Rabbits

I'd like to avoid the tedious process of hardcoding every potential scenario as I have many more variables to search through for the 'Magic Number' and they can change month to month so I'd like to only have to update the range if any new variables occur.

Thank you for your help in advance

Re: Searching for values across a range of variables

ballardw — Thu, 09 Apr 2020 15:54:15 GMT

How do you expect to put the "changing" variables into the code?

Will your "date" variable ever have multiple records with the same date?

Re: Searching for values across a range of variables

kelxxx — Thu, 09 Apr 2020 15:55:49 GMT

data have;
input no a1 a2 a3 x;
cards;
1 2 3 4 6
2 9 27 8 10
3 2 9 0 19
;
run;

data want;
set have;
array a{3} a1-a3;
array z{3} $ _TEMPORARY_ ("a1" "a2" "a3");
length k $4;
_k="";
_j=0;
do _i=1 to 3;
	if a[_i] GE x-2 and a[_i] le x+2 then do;  
		_k=trim(_k)||trim(z[_i]);
		_j+1;
	end;
end;
if _j=0 then match="NONE";
else if _j=1 then match=_k;
else match="MULT";
drop  _:;
run;

Hello,

I have a idea like that

for list of variable, you can use a macrovariable to more dynamize

Hope can help you.

Re: Searching for values across a range of variables

Tom — Thu, 09 Apr 2020 16:02:56 GMT

Look into the VNAME() function. You can use it to simplify this program.

Re: Searching for values across a range of variables

Tom — Thu, 09 Apr 2020 16:09:43 GMT

Use an array. Test the difference. Keep track of whether you have already found a match.

data have;
  input Date :$12. Cats Dogs Rabbits Magic ;
cards;
January 8 4 1 10
February 2 6 10 5
March 15 22 5 8
April 20 8 6 12
May 15 4 4 16
June 0 7 11 2
July 4 4 4 4
August 9 7 3 7
September 25 12 9 5
October 6 1 0 10
November 16 10 40 14
December 3 1 8 11
;

data want ;
  set have ;
  array list Cats Dogs Rabbits;
  length match $32;
  match='None';
  do index=1 to dim(list) until(match='Multiple');
    if  0 <= abs(list[index]-Magic) <= 2 then do;
      if match='None' then match=vname(list[index]);
      else match='Multiple';
    end;
  end;
  drop index;
run;

Obs    Date         Cats    Dogs    Rabbits    Magic    match

  1    January        8       4         1        10     Cats
  2    February       2       6        10         5     Dogs
  3    March         15      22         5         8     None
  4    April         20       8         6        12     None
  5    May           15       4         4        16     Cats
  6    June           0       7        11         2     Cats
  7    July           4       4         4         4     Multiple
  8    August         9       7         3         7     Multiple
  9    September     25      12         9         5     None
 10    October        6       1         0        10     None
 11    November      16      10        40        14     Cats
 12    December       3       1         8        11     None

Re: Searching for values across a range of variables

xezus — Thu, 09 Apr 2020 17:52:40 GMT

I did a poor job explaining how the variables would be changing. Essentially there could just be new variables added or dropped each month and I believe they go in the database in some method of alphabetic/numeric order as they are assigned unique keys. So I think as long as use an array and assure the first variable and the last variable in my dataset remain, then it should capture any new additions for the month.

There won't be any instance of multiple records with the same date.