Call a column using loop

Solved
Highlighted
Occasional Contributor
Posts: 5

Call a column using loop

I have data something like this
f1 f2 f3 f4 f5 f6 ... f500
1 2 3 8 4 6 ... 4
I want data
1 2 3
8 4 6
...
How to write sas code? Thank you.

Accepted Solutions
Solution
a week ago
Super User
Posts: 10,280

Re: Call a column using loop

First solution: use a macro loop to create multiple statements that dissect the variables:

``````data have;
input f1 :yymmdd10. f2 f3 :\$10. f4 :yymmdd10. f5 f6 :\$10.;
cards;
2018-07-09 3 XXXXXXX 2018-07-10 5 YYYYYYY
;
run;

%macro create_datastep(maxvar);
data want;
set have;
format
f_1 yymmddd10.
f_2 5.
f_3 \$10.
;
%do i = 1 %to &maxvar./3;
%let i1 = %eval(&i. * 3 - 2);
%let i2 = %eval(&i. * 3 - 1);
%let i3 = %eval(&i. * 3);
f_1 = f&i1.;
f_2 = f&i2.;
f_3 = f&i3.;
output;
%end;
keep f_1 f_2 f_3;
run;
%mend;

%create_datastep(6)``````

A probably more "elegant" solution would create three arrays in similar %do loops, and then iterate in a data step do loop.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Super User
Posts: 10,280

Re: Call a column using loop

Define an array for f1-f500.

Loop over the array with do i = 1 to 500.

In the loop, do

``````array{mod(i,3)} = array{i};
if mod(i,3) = 0 then output;``````

Keep f1-f3.

Add some code to deal with the last group (since 500 is not a multiple of 3)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

Re: Call a column using loop

If data type in f column are difference. How can i do?
Super User
Posts: 10,280

Re: Call a column using loop

You can't unless you have a repeating pattern of types and other attributes.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

Re: Call a column using loop

Have another solution? Use while loop etc.
Super User
Posts: 10,280

Re: Call a column using loop

The problem is not the "how", it's the "what". If f1, f4 and f7 have different attributes, you can't (reliably) store the values in the same column.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

Re: Call a column using loop

Ok real sample data below
f1 f2 f3 f4 f5 f6 ... f567 format is f1, f4, f7 = date, f2, f5, f8 = number and f3, f6, f9 = text repeat this until f567 i want arrang data follow my topic. Thank your for advise.
Solution
a week ago
Super User
Posts: 10,280

Re: Call a column using loop

First solution: use a macro loop to create multiple statements that dissect the variables:

``````data have;
input f1 :yymmdd10. f2 f3 :\$10. f4 :yymmdd10. f5 f6 :\$10.;
cards;
2018-07-09 3 XXXXXXX 2018-07-10 5 YYYYYYY
;
run;

%macro create_datastep(maxvar);
data want;
set have;
format
f_1 yymmddd10.
f_2 5.
f_3 \$10.
;
%do i = 1 %to &maxvar./3;
%let i1 = %eval(&i. * 3 - 2);
%let i2 = %eval(&i. * 3 - 1);
%let i3 = %eval(&i. * 3);
f_1 = f&i1.;
f_2 = f&i2.;
f_3 = f&i3.;
output;
%end;
keep f_1 f_2 f_3;
run;
%mend;

%create_datastep(6)``````

A probably more "elegant" solution would create three arrays in similar %do loops, and then iterate in a data step do loop.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

Re: Call a column using loop

Posted in reply to KurtBremser
thank you. i will try.
Super User
Posts: 10,280

Re: Call a column using loop

It's of course best to fix such an issue when reading from an external file:

``````data want2;
input f_1 :yymmdd10. f_2 f_3 :\$10. @@;
format f_1 yymmddd10.;
cards;
2018-07-09 3 XXXXXXX 2018-07-10 5 YYYYYYY
;
run;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
• 9 replies
• 158 views
• 0 likes
• 2 in conversation