Help using Base SAS procedures

Call a column using loop

Accepted Solution Solved
Reply
Highlighted
Occasional Contributor
Posts: 5
Accepted Solution

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

View solution in original post


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