CODEWITHSUNDEEP
sas
Rub
Rub

Reputation: 175

Creating a column scanning a list

Supose i have a macro variable that is a list of words

%LET myvariable= Avocado Banana Rice Corn Mango Milk Strawberry Banana Banana Lime;

Now, i wish to have a dataset where each row is one word of that list, plus a ID number

ID    Ingredient
1     Avocado
2     Banana
3     Rice
4     Corn
5     Mango
6     Milk
7     Strawberry
8     Banana
9     Banana
10    Lime

I tried something like this:

DATA food;
DO id = 1 TO 10;
Ingredient= SCAN(&myvariable.,id,' ');
    OUTPUT;
END;
RUN;

But this generated an error: "ERROR 388-185: Expecting an arithmetic operator."

This seems like a trivial thing to do, but somehow i'm stuck. So any help would be greatly appreciated.

Some background:

In my real dataset, this macro variable is created via an PROC SQL, from a dataset where each entry have several words, separated by an space. Something like this:

Product_ID    Ingredients
1             Sugar Milk Vanilla
2             Corn Sugar Banana
3             Apple Banana Maple_Syrup Oats
...           ...

Upvotes: 3

Views: 1153

Answers (2)

Robert Penridge
Robert Penridge

Reputation: 8513

No need for the macro variables and all that other stuff. You can do this directly from a datastep:

**
** CREATE SOME SAMPLE DATA
*;
data sentences;
  infile datalines truncover;
  input Product_ID    $
        Ingredients   $100.
        ;
datalines;
1 Sugar Milk Vanilla
2 Corn Sugar Banana
3 Apple Banana Maple_Syrup Oats
;
run;


**
** OUTPUT EACH WORD AS A ROW
*;
data words;
  length word $100;
  set sentences;
  retain id 0;

  cnt = 1;
  word = scan(ingredients,cnt);
  do while (word ne '');
    id = id + 1;
    output;
    cnt = cnt + 1;
    word = scan(ingredients,cnt);
  end;

  drop ingredients cnt;

run;

Upvotes: 3

BellevueBob
BellevueBob

Reputation: 9618

Very close. Without commenting on why you want to do this, you just need to put double-quotes around your macro variable name:

DATA food;
   DO id = 1 TO 10;
      Ingredient= SCAN("&myvariable.",id,' ');
      OUTPUT;
   END;
RUN;

Upvotes: 4

Related Questions