Using Arrays and Do Loops to avoid redunancies

[kmin87]

//hi i am trying to pull only certain columns. There are several columns that is a history of the records of each row. 

data practice;

set fdata1.test_weekly;
where sc1 in('A', 'B', 'C')
or where sc2 in('A', 'B', 'C')
or where sc3 in('A', 'B', 'C')
or where sc4 in('A', 'B', 'C')
or where sc5 in('A', 'B', 'C')
or where sc6 in('A', 'B', 'C')
;
run;

I am trying to do above using array and I started the flow but not sure how to complete it if doable?

data practice;
set fdata1.test_weekly;
array a_sc{*} sc1-sc6;

????

run;

[CarmineVerrell]

Hello,  

Normally we ask that you provide a sample data source and the result you are trying to accomplish.

It is not clear what you are trying to accomplish with arrays.

[kmin87]

The sample data has a lot of information and rows
in his hypothetic data set fdata1.test_weekly;
ID NAME sc1 sc2 sc3 sc4 sc5 sc6
1 Bob D A E F G H
2 Sam A D F H I
3 Mike B D E F G
4 Al
5 Steve
6 Sara
7 Tom
8
etc etc

[Reeza]

WHERE cannot use array logic so you will lose efficiency.

[kmin87]

so what i can do instead of where?

I am trying to pull data from AX1 to AX3 if they have A,B, or C.

where ACX1 in('ST', 'SU', 'SW')
or ACX2 in('ST', 'SU', 'SW')
or ACX3 in('ST', 'SU', 'SW')
or ACX4 in('ST', 'SU', 'SW')
or ACX5 in('ST', 'SU', 'SW')
or ACX6 in('ST', 'SU', 'SW')
;

how do i convert this or accomplish above by using array?

[CarmineVerrell]

I had to recreate the sample code that you send as it did not work.  You have not replied stating the result you are trying to accomplish so not sure if this answers your question.

data test_weekly;
infile cards;
input ID NAME $ sc1 $ sc2 $ sc3 $ sc4 $ sc5 $ sc6 $ ;
array sc{6};

do i=1 to 6;
if sc(i) in('A', 'B', 'C') then
do;
/* insert code here*/
end;

end;

cards;
1 Bob D A E D A E
2 Sam A D F D A E
3 Mike B D E D A E
4 Al D E E D A E
5 Steve A S D D A E
6 Sara A E E D A E
7 Tom S S D D A E
run;

[kmin87]

what do i put in the insert code?

where ACX1 in('ST', 'SU', 'SW')
or ACX2 in('ST', 'SU', 'SW')
or ACX3 in('ST', 'SU', 'SW')
or ACX4 in('ST', 'SU', 'SW')
or ACX5 in('ST', 'SU', 'SW')
or ACX6 in('ST', 'SU', 'SW')
;

How do i accomplish above by using array?

[ballardw]

---

@kmin87 wrote:

//hi i am trying to pull only certain columns. There are several columns that is a history of the records of each row. 

data practice;

set fdata1.test_weekly;
where sc1 in('A', 'B', 'C')
or where sc2 in('A', 'B', 'C')
or where sc3 in('A', 'B', 'C')
or where sc4 in('A', 'B', 'C')
or where sc5 in('A', 'B', 'C')
or where sc6 in('A', 'B', 'C')
;
run;

I am trying to do above using array and I started the flow but not sure how to complete it if doable?

data practice;
set fdata1.test_weekly;
array a_sc{*} sc1-sc6;

????

run;

---

"Pull in certain columns" usually would be a KEEP data set option or statement.

WHERE statements only use variables from the input data set(s). Any attempt to use an array reference in WHERE

is going to generate an error.

data junk;
  set sashelp.class;
  array n (*) height weight;
  where n[1] > 60
     or n[2] > 100
  ;
run;

Make up some dummy data that you can share. Show us a data step to replicate that data.

Then describe some rules and show the result based on that dummy data.

You might use a different comparison to explicitly OUTPUT.

data practice;
   set fdata1.test_weekly;
   array n (*) sc1 - sc6;
   array v (3) $ 1 _temporary_ ('A','B','C');
   do i= 1 to dim(v);
      if whichc(v[i], of n(*))>0 then do;
         output;
         leave;
      end;
   end;
  drop i;
;
run;

The temporary array holds explicit values to test. The loop outputs to the data set when the value is found in the array of variables. The Leave then terminates the DO loop.

HOWEVER, all the variables and all of the values will have to be of the same type for this approach to work.

Any approach that breaks up the comparisons has a potential for generating duplicate output when using the OUTPUT statement. Also, any other manipulation of variables must be performed before the OUTPUT or the results are not written to the data set.

[kmin87]

I am just trying to keep columns, sc1 through sc5 that have just the values 'a','b', or 'c' by using arrays and not have entries that do not meet the criteria

hence why i was doing

set fdata1.test_weekly;
where sc1 in('A', 'B', 'C')
or where sc2 in('A', 'B', 'C')
or where sc3 in('A', 'B', 'C')
or where sc4 in('A', 'B', 'C')
or where sc5 in('A', 'B', 'C')
or where sc6 in('A', 'B', 'C')
;
run;

[Reeza]

COLUMNS or ROWS?

What you're doing there will affect your ROWS not your COLUMNS.

Please clarify your requirements.

[kmin87]

rows.

[ballardw]

---

@kmin87 wrote:

I am just trying to keep columns, sc1 through sc5 that have just the values 'a','b', or 'c' by using arrays and not have entries that do not meet the criteria

hence why i was doing

set fdata1.test_weekly;
where sc1 in('A', 'B', 'C')
or where sc2 in('A', 'B', 'C')
or where sc3 in('A', 'B', 'C')
or where sc4 in('A', 'B', 'C')
or where sc5 in('A', 'B', 'C')
or where sc6 in('A', 'B', 'C')
;
run;

---

Here's an interesting question: Did you even TRY the code I proposed.

WHERE is not the ONLY way to get specific records in an output data set.

Sub-setting:

if varname= 'somevalue';

will restrict the records kept.

Explicit OUTPUT when conditions are met.

So TRY proposed solutions before posting an approach you don't want to use.

[kmin87]

I did and it produced 0 observations

[ballardw]

Then you need to show some data.

Likely causes:

your actual values are not A B C. The code is using exact comparisons. If your actual values are 'a' 'b' 'c' then you can't find 'A'.

Or your actual values have leading spaces ' A', same problem just not as obvious.

The values we were told to look for are not in your data in the variable names provided.

Code was mistyped or similar.

You need to provide some details so we can help you.

Example using a modified version of a data set above:

data test_weekly;
infile cards missover;
input ID NAME $ sc1 $ sc2 $ sc3 $ sc4 $ sc5 $ sc6 $ ;
cards;
1 Bob D A E D A E
2 Sam A D F D A E
3 Mike D D E D D E
4 Al D E E D A E
5 Steve A S D D A E
6 Sara A E E D A E
7 Tom S S D D D E
8 John
;


data practice;
   set test_weekly;
   array n (*) sc1 - sc6;
   array v (3) $ 1 _temporary_ ('A','B','C');
   do i= 1 to dim(v);
      if whichc(v[i], of n(*))>0 then do;
         output;
         leave;
      end;
   end;
  drop i;
;
run;

Keeps rows 1, 2, 4, 5 and 6. Excludes 3 and 7 because none of the values are in the list and 8 because it has no values for any of the variables.

Show the LOG of the code you submitted. Copy the log text, open a text box on the forum using the </> icon above the message window, and paste the copied text.