topic Re: Horizontal loop in SAS Programming

Horizontal loop

Garik — Tue, 24 Oct 2017 06:17:43 GMT

Hi, I have a question. How can I write a code in order to loop horizontally.

I must sum the values of the X1 - X10 {S = sum(of X1- X10)} but depending on their value.

For instance, X{i} is omitted if it is less than 5.

or,

if the column name equals "Class_A" then the appropriate value of the column must be added to the sum.

Thanks.

Re: Horizontal loop

error_prone — Tue, 24 Oct 2017 06:39:04 GMT

You will want to look up the syntax of array, iterative do-loop and the if-statement.

Post the code you have.

Re: Horizontal loop

RW9 — Tue, 24 Oct 2017 09:04:33 GMT

data want;
  set have;
  array x{10};
  do i=1 to 10;
    s=s+ifn(x{i}>5,x{i},0);
  end;
run;

Re: Horizontal loop

ChrisBrooks — Tue, 24 Oct 2017 10:15:41 GMT

Whenever I see someone doing/wanting to do a horizontal loop I immediately say to myself "that's spreadsheet thinking". In the long run you're much better off transposing your variables so you have a long and thin data set. For example what if, next time you want to run your code you have 15 variables instead of 10? You'll have to edit your code and that will become tedious and error-prone over time - much better to transpose it and use by-group processing which will remove that issue.

Re: Horizontal loop

Garik — Tue, 24 Oct 2017 13:27:23 GMT

Hi. if the dataset contains 20 mln rows and 245 columns! would the transposing work?

Re: Horizontal loop

Kurt_Bremser — Tue, 24 Oct 2017 13:46:45 GMT

Transposing from wide to long is no problem, as SAS only needs to treat one record at a time. And if you can set a rule for values that can be dropped (ie missing or 0), you might even end up with a physically smaller dataset.

Re: Horizontal loop

ChrisBrooks — Tue, 24 Oct 2017 15:40:05 GMT

As @Kurt_Bremser says a long data set will (in my experience) usually be more efficient than a wide one even with more rows. Having said that if you were my client I'd be looking very hard at this data - with 20 million rows and 250 variables there's probably room for some reorganisation which would make processing more efficient and save on space.

I've been doing this sort of thing professionally for 30 years and turned numerous clients data around from wide to long - they're usually sceptical at first but once they see the transposed data sets in action they're quickly turned around 🙂