topic Re: Merging using a counter in proc sql in SAS Programming

Merging using a counter in proc sql

SidS — Tue, 30 Apr 2013 03:16:35 GMT

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

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!

Re: Merging using a counter in proc sql

Ksharp — Tue, 30 Apr 2013 03:45:45 GMT

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

Re: Merging using a counter in proc sql

SidS — Thu, 02 May 2013 04:11:47 GMT

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.

Re: Merging using a counter in proc sql

Ksharp — Thu, 02 May 2013 04:42:55 GMT

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

Re: Merging using a counter in proc sql

AndersSkollermo — Thu, 02 May 2013 19:51:53 GMT

I agree totally with Ksharp! / Br Anders

Re: Merging using a counter in proc sql

Tom — Thu, 02 May 2013 20:46:56 GMT

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.