BookmarkSubscribeRSS Feed
SidS
Calcite | Level 5

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!

5 REPLIES 5
Ksharp
Super User

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

SidS
Calcite | Level 5

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
Super User

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
Calcite | Level 5

I agree totally with Ksharp!  / Br Anders

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 620 views
  • 0 likes
  • 4 in conversation