DATA Step, Macro, Functions and more

Horizontal loop

Reply
Contributor
Posts: 40

Horizontal loop

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. 

Regular Contributor
Posts: 213

Re: Horizontal loop

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

Post the code you have.
Super User
Super User
Posts: 9,599

Re: Horizontal loop

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

Re: Horizontal loop

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.

Contributor
Posts: 40

Re: Horizontal loop

Posted in reply to ChrisBrooks

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

Super User
Posts: 10,279

Re: Horizontal loop

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 595

Re: Horizontal loop

Posted in reply to KurtBremser

As @KurtBremser 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 :-)

Ask a Question
Discussion stats
  • 6 replies
  • 221 views
  • 6 likes
  • 5 in conversation