How do you iterate through dataset columns?

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

How do you iterate through dataset columns?

[ Edited ]

Hello all,

 

I am trying to iterate through various fields within a SAS dataset that share common column headings i.e.

Dataset column headings: Col1Test, Col2Test, Col3Test, Col4Test, Col5Test

 

This is the code that I have so far (please see the line in bold text for where I am experiencing my problem - is this possible? - how can I resolve this?):

 

 

DATA TEMP.TEMPTEST;
	do _n_ = 1 by 1 until(last.COLTEST);
		set TEMP.TEMP2;
		by COLTEST;
		do i = 1 to 10;
			COL(i) = TOTAL;
		END;
	END;
RUN;

Many thanks

 


Accepted Solutions
Solution
‎11-11-2016 07:36 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,240

Re: How do you iterate through dataset columns?

Well, your columns are not setup in an ideal manner for this, normally the numeric should be a suffix, i.e. coltest1, coltest2, so it is easy to access them.  So you will need to bash it a bit, this assumes that the columns start at col1test and got to 10 - note also that as you have not provided any test data in a datastep I can't test this, only guessing:

data temp.temptest;
  do _n_ = 1 by 1 until(last.coltest);
    set temp.temp2;
    by coltest;
    array col{10} col1test--col10test;
    do i = 1 to 10;
      col(i) = total;
    end;
  end;
run;

Also, can't see what most of this code is for, again no test data, nor required output, but the whole do _n_=... loop doesn't actually seem to do anything, all you are doing is setting all colXtest columns to a variable called total.

View solution in original post


All Replies
Solution
‎11-11-2016 07:36 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,240

Re: How do you iterate through dataset columns?

Well, your columns are not setup in an ideal manner for this, normally the numeric should be a suffix, i.e. coltest1, coltest2, so it is easy to access them.  So you will need to bash it a bit, this assumes that the columns start at col1test and got to 10 - note also that as you have not provided any test data in a datastep I can't test this, only guessing:

data temp.temptest;
  do _n_ = 1 by 1 until(last.coltest);
    set temp.temp2;
    by coltest;
    array col{10} col1test--col10test;
    do i = 1 to 10;
      col(i) = total;
    end;
  end;
run;

Also, can't see what most of this code is for, again no test data, nor required output, but the whole do _n_=... loop doesn't actually seem to do anything, all you are doing is setting all colXtest columns to a variable called total.

Contributor
Posts: 25

Re: How do you iterate through dataset columns?

[ Edited ]

Thank you for your response. I suppose that I should have included a bit more information however I was attempting to try and understand if the concept was possibile. Essentially I have to iterate through 10 columns in a dataset and set each columns data to a specific value based on a set of conditions. 

 

This is the original code that works however you can see that it is not optimised. 

 

 

data TEMP.CARD_AI_2_JOIN (drop=ACC_NUM );
	do _n_ = 1 by 1 until(last.PAN_N);
		set TEMP.CARD_AI_LOOK_UP;
		by PAN_N;

		if i = 1 then
			do;
				ACNT_1_A = ACNT_N_TOTAL;
				ACNT_1_B = ACNT_TYPE_TOTAL;
				ACNT_1_C = ACNT_STTS_TOTAL;
				ACNT_1_D = ACNT_DESC_TOTAL;
			end;

		if i = 2 then
			do;
				ACNT_2_A = ACNT_N_TOTAL;
				ACNT_2_B = ACNT_TYPE_TOTAL;
				ACNT_2_C = ACNT_STTS_TOTAL;
				ACNT_2_D = ACNT_DESC_TOTAL;
			end;

if statements to 10...

 

I would like to replace this section of code with an iterative step for i = 1 to 10 for each account column:

ACNT_1_A = ACNT_N_TOTAL;
ACNT_1_B = ACNT_TYPE_TOTAL;
ACNT_1_C = ACNT_STTS_TOTAL;
ACNT_1_D = ACNT_DESC_TOTAL;

 i.e. something like the following

ACNT_i_A = ACNT_N_TOTAL;
ACNT_i_B = ACNT_TYPE_TOTAL;
ACNT_i_C = ACNT_STTS_TOTAL;
ACNT_i_D = ACNT_DESC_TOTAL;
Esteemed Advisor
Posts: 6,693

Re: How do you iterate through dataset columns?

Where is i coming from?

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 25

Re: How do you iterate through dataset columns?

[ Edited ]

Good question. Apologies, I am in the process of fixing someone else's code and trying to optimise it at the same time. 

Esteemed Advisor
Esteemed Advisor
Posts: 7,240

Re: How do you iterate through dataset columns?

Yes, my code above covers that question.  However you have not answered mine.  What is the input dataset, convert it to a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

And post that here so we have a basis to work from.  Then post an example of what you want out, as currently we are guessing.  As a general thing you would use arrays to access variables either in a list, ordered, or specified. But in your code I still do not see why you have the do _n_=1... set bit as it doesn't do anything.

Contributor
Posts: 25

Re: How do you iterate through dataset columns?

Agreed, as mentioned in my last post, I am trying to fix someone else's code and I agree that the statement as it stands does not do anything. Unfortunately I cannot provide sample data as the data is highly sensitive.
Esteemed Advisor
Posts: 6,693

Re: How do you iterate through dataset columns?

data TEMP.CARD_AI_2_JOIN (drop=ACC_NUM );
set TEMP.CARD_AI_LOOK_UP;
array ACNT_A {*} ACNT_1_A-ACNT_10_A;
array ACNT_B {*} ACNT_1_B-ACNT_10_B;
array ACNT_C {*} ACNT_1_C-ACNT_10_C;
array ACNT_D {*} ACNT_1_D-ACNT_10_D;
ACNT_A{i} = ACNT_N_TOTAL;
ACNT_B{i} = ACNT_TYPE_TOTAL;
ACNT_C{i} = ACNT_STTS_TOTAL;
ACNT_D{i} = ACNT_DESC_TOTAL;
run;

Everything else you have in your code just obfuscates a rather simple logic, especially the totally useless do loop.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 4,995

Re: How do you iterate through dataset columns?

The heavy lifting here could be done by an ARRAY statement before the loop begins.  For example:

 

array A_vals {10, 4} acnt_1_a acnt_1_b acnt_1_c acnt_1_d

acnt_2_a acnt_2_b acnt_2_c acnt_2_d

acnt_3_a acnt_3_b acnt_3_c acnt_3_d

acnt_4_a acnt_4_b acnt_4_c acnt_4_d

 

....

acnt_10_a acnt_10_b acnt_10_c acnt_10_d;

 

Then inside the loop all you need to code (following the BY statement) is:

 

A_vals{ i, 1} = acnt_n_total;

A_vals{ i, 2} = acnt_type_total;

A_vals{ i, 3} = acnt_stts_total;

A_vals{ i, 4} = acnt_desc_total;

 

It's untested code, but looks like the right tool for the job.

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 320 views
  • 0 likes
  • 4 in conversation