DATA Step, Macro, Functions and more

Lots of variables and columns in a select when statement - help?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Lots of variables and columns in a select when statement - help?

I have a dataset where I would like to add a new column assigned 'bruks' where if 'champ1', 'champ2', 'champ3' 'champ4' 'champ5' or 'champ6' have any of the following observations;

50 161 183 196 264 331 633 746 798 103 163 524 525 526, it should be assigned as 1 in 'bruks', if neither, it should be assigned 0 in 'bruks'. I really have trouble finding the right type of statement for this, I understand that if, then is not proper with this amount of variables and columns, but cannot seem to make the select -when statement to work properly wither (I amcoding it wrong, obviously Smiley Wink)

 

Can anyone help, please?

 

One of my attempts here ( I tried both with end; in all lines, and without, and this only shows the first two *champ*categories):

 

data rrmh.bruks;

set rrmh.etresultat;

select (champ1);

when (50) do; bruks=1;

when (161) do; bruks=1;

when (183) do; bruks=1;

when (196) do; bruks=1;

when (264) do; bruks=1;

when (331) do; bruks=1;

when (633) do; bruks=1;

when (746) do; bruks=1;

when (798) do; bruks=1;

when (103) do; bruks=1;

when (163) do; bruks=1;

when (524) do; bruks=1;

when (525) do; bruks=1;

when (526) do; bruks=1;

select (champ2);

when (50) do; bruks=1;end;

when (161) do; bruks=1;end;

when (183) do; bruks=1;end;

when (196) do; bruks=1;end;

when (264) do; bruks=1;end;

when (331) do; bruks=1;end;

when (633) do; bruks=1;end;

when (746) do; bruks=1;end;

when (798) do; bruks=1;end;

when (103) do; bruks=1;end;

when (163) do; bruks=1;end;

when (524) do; bruks=1;end;

when (525) do; bruks=1;end;

when (526) do; bruks=1;end;

otherwise; bruks=0;

end;

run;

 

 

Thanks in advance!


Accepted Solutions
Solution
‎11-03-2015 03:43 PM
Super User
Posts: 17,868

Re: Lots of variables and columns in a select when statement - help?

[ Edited ]

Also the IN operator

bruks=0;

if champs1 in (50 161 183 196 264 331 633 746 798 103 163 524 525 526) then bruks=1;

Be careful with the otherwise. If you find a value in champ1 for example and then run the second set of Select statements it would reset it 0.

View solution in original post


All Replies
Contributor ndp
Contributor
Posts: 61

Re: Lots of variables and columns in a select when statement - help?

Using an array would be much better. let us know if you need help with coding.

Occasional Contributor
Posts: 10

Re: Lots of variables and columns in a select when statement - help?

Yes, I absolutely need help with the coding Smiley Very Happy

 

I am a beginner to SAS, and mostly change programs others have already coded, so this is new to me!

Solution
‎11-03-2015 03:43 PM
Super User
Posts: 17,868

Re: Lots of variables and columns in a select when statement - help?

[ Edited ]

Also the IN operator

bruks=0;

if champs1 in (50 161 183 196 264 331 633 746 798 103 163 524 525 526) then bruks=1;

Be careful with the otherwise. If you find a value in champ1 for example and then run the second set of Select statements it would reset it 0.

Occasional Contributor
Posts: 10

Re: Lots of variables and columns in a select when statement - help?

So I can write:

 

bruks=0;

if champ1 in (50 161 183 196 264 331 633 746 798 103 163 524 525 526) then bruks=1;

if champ2 in (50 161 183 196 264 331 633 746 798 103 163 524 525 526) then bruks=1;

if champ3 in (50 161 183 196 264 331 633 746 798 103 163 524 525 526) then bruks=1;

and so on?

Super User
Posts: 17,868

Re: Lots of variables and columns in a select when statement - help?

You could, but should use an array. Since you have only 5 variables you don't save much in code, but if you had 20 it saves a lot. It also makes it easier to update if you need to change that IN list and prevents errors.

array chmp(5) champ1-champ5;
bruks=0;
do i=1 to dim(chmp);
if chmp(i) in (50 161 183 196 264 331 633 746 798 103 163 524 525 526) then bruks=1;
end;
Occasional Contributor
Posts: 10

Re: Lots of variables and columns in a select when statement - help?

It was the "in" that I wasn't aware of, that ruined my IF-THEN attempt.

 

But I see that your last answer is better for more variables, yes. What does the "dim" stand for?

Super User
Posts: 17,868

Re: Lots of variables and columns in a select when statement - help?

DIM() is a function that calculates how many variables in the array. In this case it would return 5.
Occasional Contributor
Posts: 10

Re: Lots of variables and columns in a select when statement - help?

If the columns had different names (champ, many, few, lot, none), how would the code in the first line look? And I might reveal myself as a total ignorant now, but when one has stated that bruks=1 for all the mentioned numbers, what does the do i=1 to dim(chmp); do?

 

array chmp(5) champ1-champ5;
bruks=0;
do i=1 to dim(chmp);
if chmp(i) in (50 161 183 196 264 331 633 746 798 103 163 524 525 526) then bruks=1;
end;

Super User
Posts: 17,868

Re: Lots of variables and columns in a select when statement - help?

An array sets up the list of variables that will be looped through. So you would list all your variables after the array name.

Array <arrayname>(<num elemements>) list_of_variables;


The Do/End is a loop to loop through all variables set up in the array.

You may want to review some papers on Lexjansen.com on array processing.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 329 views
  • 3 likes
  • 3 in conversation