create new variables with loop and scan function?

[ShawnZ]

Dear all,

I'm very new to the loops and macro, I've been searching for the answers all day but couldn't do it right in SAS.

I want to break down my variables into different parts and make them into new variable, say the variable is people's name or address, and can have space and comma among the words.

This what I did for example:

1. How can I use a loop to do it? any macro needed? or is there any other simple way?

data APPLE;

set TEST;

NEW1 = scan (NAME1, 1);

NEW2 = scan (NAME1, 2);

NEW3 = scan (NAME1, 3);

...

NEWn = scan (NAME1, n)

run;

2. If I have another variable, for example NAME2, can it be done with loop as well?

data APPLE;

set TEST;

NEW1 = scan (NAME1, 1);

NEW2 = scan (NAME1, 2);

NEW3 = scan (NAME1, 3);

...

NEWn = scan (NAME1, n)

NEW(n+1) = scan (NAME2, 1);

NEW(n+2) = scan (NAME2, 2);

...

NEW(n+m) = scan (NAME2, m);

run;

Thank you so much for the help!

[Tom · Accepted solution]

Sounds like a job for an ARRAY.

data APPLE;

set TEST;

array new (10) $40 ;

do i=1 to dim(new);

   NEW(i) = scan (NAME1, i);

end;

run;

[Ksharp]

1) make it vertical and then use proc transpose.

2)pre-detect the number of variables you need to build.

these two ways could make you not guess the number of variables firstly.

Ksharp

[ShawnZ]

Thanks all, that was very helpful!

Can I bring the question to further steps?

1. like my second question above, if I want to repeat the 'scan' step for some other variables like 'NAME2' or maybe even 'NAME3', do I need start a new array?

2. I want to create some new variables to categorize the ones created by array. For example there NAME1 may have obs like 'Fuji', Fuji MT', 'Gala', 'MT Gala'...I want to do something like this:

if NEW(i) = 'Fuji' and NEW(i) ne 'MT'

  then Fuji =1;

else if NEW(i) = 'Fuji' and NEW(i) = 'MT'

  then Fuji =2;

else Fuji = 0;

if NEW(i) = 'Gala' and NEW(i) ne 'MT'

  then Gala =1;

else if NEW(i) = 'Gala' and NEW(i) = 'MT'

  then Gala =2;

else Gala = 0;

is there anyway I can put the above into the array?

Thanks a lot!

[Tom]

1) Depends what you want to do. For what you want to just you could use the same loop put replace NAME with catx(' ',of name1-name3) in the SCAN function.

2) That is harder to do.   To make your IF clauses make sense you want want one to have a higher index than the other.

new(i)='Gala' and new(i+1)='MT' for example.

But the logic could get very complex to find all possible combinations.

You might want to consider pre-processing the string first.

Something like: 

name = tranwrd(name,'MT Gala','Gala') ;

name = tranwrd(name,'Gala MT','Gala');