The main difference when you've only got one source dataset is that the Extract transformation doesn't support having. But the extra flexibility (ease of changing procedure and dataset options, for example) make it more superior again. I still use Extract, but only when I know that the requirements for using it are simple and will (in all likelihood) not change.
Personally, when I'm writing SAS by hand I tend towards using a data step, because of the control and often sheer speed it gives me.
When joining two or more tables together, I chop and change between an SQL join and a dataset merge/update. It depends on the structure (especially width) and overall size of the datasets. I do have a generic DI transformation that I've written that does a dataset merge - it requires that the two source datasets are sorted. Even with massively denormalised tables on both sides, it screams through - something that SQL has trouble doing.
@LaurieF, how would this logic work of the rows have different values in the grouped columns?
For example:
A B C D 10 A B C D 10 A B C D 30 A B C D 50
A B H D 10
A B H D 10
A B H D 10
A B H D 20
F F F D 10
F F F D 30
With the above and doing a max() on the fifth column...the value in the 5th column for all rows will be 50, which is the highest. Seemingly even when I group the first four. I get:
A B C D 50
A B H D 50
F F F D 50
What I desire would be:
A B C D 50
A B H D 20
F F F D 30
I guess I would think that the max() would evaluate for the highest value within the group.
Is that not correct?
Thanks!
If the other columns are numeric and amenable to statistic such as max or min (and few others) as an alternate approach:
proc summary data=have nway;
class col1 col2 col3 col4 ;
var var1 var2 var3;
output out=want(drop = _type_ _freq_) max= ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.