04-29-2013 11:16 PM
I am trying to start using proc sql because I have extremely large datasteps which take forever to sort and merge. This is the format of my data:
The final result should be
ID WrongTimeStamp RightTimeStamp
1 12:00 12:15
1 12:00 13:05
2 13:00 14:05
Essentially I need to create a counter variable for each of a1 and a2. Then I need to merge by ID and this counter variable. Any ideas on how to do this proc sql? I don't want to create the counter variables in a SAS data step because that would require a proc sort and defeat the whole purpose.
Thanks so much!
05-02-2013 12:11 AM
Why is SQL not suitable? From the several articles I have read on PROC SQL, I gather that it helps speed up things tremendously. None have ever mentioned that the data step is more suitable for large data.
05-02-2013 12:42 AM
SQL is designed based on Cartesian Product , which means if you have 10000 obs ,then it will generate 10000*10000 obs, Huge ? and
more important thing is SQL will load these obs into your memory to process, at sometime your memory will explode when you have a huge dataset.
05-02-2013 04:46 PM
PROC SQL doesn't prevent sorting, it just means the programmer doesn't have to type extra code to get the data sorted.
PROC SQL is NOT good at generating such a counter variable.