array using a list in another data

[lillymaginta]

Hi All,

I have the following code: 

data a (drop=i);

  set b;

  array x{*} dx:;

vcf2=0;

do i=1 to dim(x);

vcf2= ifn(substrn(x{i},1,5)in ( **),1,vcf2);

end;

run;

Is there a way to put instead of the start a long list of numbers that are in another subset of data? so for example

vcf2= ifn(substrn(x{i},1,5)in (  variable c in work.c                                  ),1,vcf2);

[Reeza]

If your data was in a long format then yes, this would be trivial. It's probably easiest to transpose your data and use that method.

If you want to stay vertical, you can load your second data set into a temporary array and use that. You would likely want to have two nested loops, looping of all the conditions and all of the diagnosis, or use WHICHC. 

There's a basic example of a temporary array here and loading it from a data set:

https://gist.github.com/statgeek/f052b5223fecca066b1f

It's not a really great example but hopefully gives you the idea of how to load the data set and use it. 

[PaigeMiller · Accepted solution]

You can't mix and match DATA step code and SQL code. In a DATA step, you can only use DATA step code.

You could do something like this:

vcf2= ifn(substrn(x{i},1,5) in ('horse','sheep','robin'),1,vcf2);

but of course this requires a lot of typing and avoiding making typing errors.

If the values are stored in a data set (in other words, you have a data set with a column named NAME, and the value in row 1 is horse and the value in row 2 is sheep and so on, then you could create a macro variable named &names that contains the list you want

data mylistdataset;
    input name $;
cards;
horse
sheep
robin
;

proc sql noprint;
    select distinct quote(trim(name)) into :names separated by ',' from mylistdataset;
quit;

data a (drop=i);
    set b;
    array x{*} dx:;
    vcf2=0;
    do i=1 to dim(x);
        vcf2= ifn(substrn(x{i},1,5)in (&names),1,vcf2);
    end;
run;

[lillymaginta]

Thank you Reeza and Paige, yes the value is stored in a dataset because it includes a list of 1000+ numbers. 

[Reeza]

Macro variables have a limit of ~65k characters. So you're 1000 numbers need to be less than 6 characters each, not including the , and quotation marks which may be an issue for you. Make sure to verify that the macro variable is created correctly.

---

@lillymaginta wrote:

Thank you Reeza and Paige, yes the value is stored in a dataset because it includes a list of 1000+ numbers. 

---

[PaigeMiller]

So the macro variable created would have 5 digit text, quotes on either side and a comma following, that's 8 characters, so the size limit for macro variables would not be exceeded unless the list had over 8000 items. If that did happen, you could either split the macro variable into pieces, each piece being less than 65K characters, or you could use CALL EXECUTE.