topic Re: Mergers in SAS Programming

Mergers

aidant01 — Sat, 01 Dec 2018 05:08:43 GMT

Hi All,

I'm trying to get the desired dataset with the following command

data wanted;

merge dataA (in=in1) dataB (in=in2);

by Country,

run;

But with no luck. any help is appreciated! Thanks!!!!

Re: Mergers

Shmuel — Sat, 01 Dec 2018 06:32:31 GMT

You have not supplied your log nor explained what issue you have.

I suspect that one or both of your data sets are not sorted properly.

Both should be sorted by same key - country - in your case:

proc sort data=dataA; by country; run;
proc sort data=dataB; by country; run;

data wanted;
merge dataA (in=in1) dataB (in=in2);
by Country;  /* you had a comma instead a semicolon */ 
run;

Re: Mergers

PGStats — Sat, 01 Dec 2018 06:53:07 GMT

@aidant01 wrote:

Hi All,

I'm trying to get the desired dataset with the following command

data wanted;

merge dataA (in=in1) dataB (in=in2);

by Country;

run;

But with no luck. any help is appreciated! Thanks!!!!

Re: Mergers

Kurt_Bremser — Sat, 01 Dec 2018 09:12:35 GMT

Maxim 2: Read the Log.

Which implies that it is always a good idea to post the log when steps do not work as expected, as it allows us to interpret your log, find the problem, and show you how to interpret SAS logs. Reading and getting information from the log is an essential SAS skill.

Also do not provide data in Excel spreadsheets; spreadsheets have no structure like datasets, and it's never given that the re-import into SAS results in a dataset like yours. Post example data in a data set with datalines.

With Excel, I need to

download the spreadsheet
open it with Excel to save as csv
copy the csv to the server
run proc import or a data step, while making guesses about the structure

With a data step, I have to

copy paste to a code window
submit the code

and there are no ambiguities with regards to structure.

The macro from one of my footnotes can do the conversion of a dataset to a data step automatically.

Re: Mergers

aidant01 — Sun, 02 Dec 2018 06:02:57 GMT

The code is:

data mergeddata;

merge datasetA (in=in1) datasetB (in=in2);

by country;

run;

Re: Mergers

Kurt_Bremser — Sun, 02 Dec 2018 08:40:18 GMT

Since you have a many-to-many relationship, and want a cartesian join, you need to use proc sql.