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.
data want; set have; array x{10}; do i=1 to 10; s=s+ifn(x{i}>5,x{i},0); end; run;
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.
Hi. if the dataset contains 20 mln rows and 245 columns! would the transposing work?
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.
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 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.