topic Re: Call a column using loop in SAS Procedures

Call a column using loop

nottp — Thu, 12 Jul 2018 10:46:08 GMT

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.

Re: Call a column using loop

Kurt_Bremser — Thu, 12 Jul 2018 11:01:18 GMT

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)

Re: Call a column using loop

nottp — Thu, 12 Jul 2018 11:03:31 GMT

If data type in f column are difference. How can i do?

Re: Call a column using loop

Kurt_Bremser — Thu, 12 Jul 2018 11:12:19 GMT

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

Re: Call a column using loop

nottp — Thu, 12 Jul 2018 11:15:40 GMT

Have another solution? Use while loop etc.

Re: Call a column using loop

Kurt_Bremser — Thu, 12 Jul 2018 11:20:21 GMT

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.

Re: Call a column using loop

nottp — Thu, 12 Jul 2018 12:09:35 GMT

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.

Re: Call a column using loop

Kurt_Bremser — Thu, 12 Jul 2018 12:20:38 GMT

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.

Re: Call a column using loop

Kurt_Bremser — Thu, 12 Jul 2018 12:23:35 GMT

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;

Re: Call a column using loop

nottp — Fri, 13 Jul 2018 00:46:35 GMT

thank you. i will try.