Horizontal loop

[Garik]

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. 

[error_prone]

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

Post the code you have.

[RW9]

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

[ChrisBrooks]

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.

[Garik]

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

[Kurt_Bremser]

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.

[ChrisBrooks]

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 🙂