BookmarkSubscribeRSS Feed
SASHelpPlease
Calcite | Level 5

Hi there,

I'm having trouble with merging two data sets - one is a large file, bcarrier_line, with over 22 million rows.  The other file is a DESY_SORT_KEY file with 344 rows (one column).  I wish to merge the two files by DESY_SORT_KEY, so bcarrier_line file only outputs rows with desired DESY_SORT_KEY values.

I've ran this code with no errors, but the log output shows no row number change upon the merge (i.e. the larger file doesn't decrease in row size).  From similar, but other, files I know the merge should significantly decrease the number of rows of the bcarrier_line data set.

CODE:

libname aaa 'M:\';
%let bcarrier = bcarrier_line;

*Already a SAS dataset;

DATA desy;
INFILE 'M:\DESY_SORT_KEY.csv';
INPUT DESY_SORT_KEY;
run;

PROC SORT data = aaa.&bcarrier;
by DESY_SORT_KEY;
run;

PROC SORT data=desy;
by DESY_SORT_KEY;
run;

DATA mergeaaa;
merge  aaa.&bcarrier
   desy;
by DESY_SORT_KEY;
run;

PROC EXPORT DATA = mergeaaa
   OUTFILE='M:\bcarrier_line_aaa.csv';
run;

Any tips or advice is most appreciated!

2 REPLIES 2
Reeza
Super User

Use SQL instead for a one step process.

PROC SQL;

Create table mergeaaa as

select * from aaa.bcarrier

where DESY_SORT_KEY in (select DESY_SORT_KEY from desy);

quit;

OR try the in option in your merge:

DATA mergeaaa;
merge  aaa.&bcarrier (in=a)
   desy(in=b);
by DESY_SORT_KEY;

if b;
run;

SASHelpPlease
Calcite | Level 5

Hi Reeza,

Thanks - got it working!

For some reason I thought the "in" command with DATA MERGE was optional.  As well, thanks for the related PROC SQL code.

Robin

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
  • 2 replies
  • 457 views
  • 1 like
  • 2 in conversation