Merging using a counter in proc sql

[SidS]

Hello,

   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:

data a1

ID    WrongTimeStamp

1      12:00

1      12:00

2     13:00

data a2

ID    RightTimeStamp

1      12:15

1      13:05

2     14:05

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!

[Ksharp]

No. SQL is not suited for extreme large table. Data step is .

[SidS]

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. 

[Ksharp]

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.

Ksharp

[AndersSkollermo]

I agree totally with Ksharp!  / Br Anders

[Tom]

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.