Merging using a counter in proc sql

Reply
Occasional Contributor
Posts: 9

Merging using a counter in proc sql

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!

Super User
Posts: 10,044

Re: Merging using a counter in proc sql

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

Occasional Contributor
Posts: 9

Re: Merging using a counter in proc sql

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. 

Super User
Posts: 10,044

Re: Merging using a counter in proc sql

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

N/A
Posts: 1

Re: Merging using a counter in proc sql

I agree totally with Ksharp!  / Br Anders

Super User
Super User
Posts: 7,076

Re: Merging using a counter in proc sql

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.

Ask a Question
Discussion stats
  • 5 replies
  • 232 views
  • 0 likes
  • 4 in conversation