Merge two sas7bdat datasets together in sas university

[stephcolter]

Please help I'm trying to merge NHANES data into one big dataset. They came as XPT files so I used this code to turn them into sas7bdat files:

libname sasfile '/folders/myfolders/SAS Dataset';

libname xptfile xport '/folders/myfolders/NHANES DATA/CDQ_J.xpt' access=readonly;
libname xptfile xport '/folders/myfolders/NHANES DATA/DBQ_J.xpt' access=readonly;
libname xptfile xport '/folders/myfolders/NHANES DATA/DEMO_J.xpt' access=readonly;
libname xptfile xport '/folders/myfolders/NHANES DATA/DXX_J.xpt' access=readonly;
libname xptfile xport '/folders/myfolders/NHANES DATA/PAQ_J.xpt' access=readonly;
libname xptfile xport '/folders/myfolders/NHANES DATA/WHQ_J.xpt' access=readonly;

proc copy inlib=xptfile outlib=sasfile;

run;

Now I want to merge all 5 datasets together using the variable SEQN that they all have in common. 

Please help I haven't used SAS in awhile so I'm probably forgetting something extremely simple

[Shmuel]

1) Having the same name in all libnames - they overide each other up to the last

    so only the last one in in affect. You need to assign different names to each.

2) Proc copy will copy each file to the output library.

    It will not merge them, They will still be separated in the new library.

3) Better not insert space in a folder name - "SAS dataset" should be "SAS_Dataset";

4) the variable SEQN has nothing to do with proc copy.

[stephcolter]

im not trying to import they are already sas7bdat files now... i just need to merge the four datasets by SEQN 

[Shmuel]

The proc copy will transfer the internal sas dataset format from xpt engine to sas v8 (or newer) format, then you can merge the datsets.

Any way you need first to copy them, one by one, to the sasfile library, as shown in the link.

You may try next code, with hope it will be shorter to code:

libnmae xptfile xport 
        ('/folders/myfolders/NHANES DATA/CDQ_J.xpt'
		 '/folders/myfolders/NHANES DATA/DBQ_J.xpt'
		 '/folders/myfolders/NHANES DATA/DEMO_J.xpt'
		 '/folders/myfolders/NHANES DATA/DXX_J.xpt'
		 '/folders/myfolders/NHANES DATA/PAQ_J.xpt'
		 '/folders/myfolders/NHANES DATA/WHQ_J.xpt')
	 access=readonly;

libname sasfile '/folders/myfolders/SAS Dataset';
proc copy inlib=xptfile outlib=sasfile; run;

data temp; 
 set sasfile.CDQ_J
     sasfile.DBQ_J
     sasfile.DEMO_J
     sasfile.DXX_J
     sasfile.PAQ_J
     sasfile.WHQ_J
 ;
run;
proc sort data=temp out=want /*NODUPKEY*/;
  by SEQN;
run;

Note - use the sort NODUPKEY option if there may be more the one observation with the same SEQN value and you want only one observation. 

You can check if there are duplicates by next code:

data dups;
 set want;
  by seqn;
      if not (first.seqn and last.seqn) ;    /* meand SEQN is not unique */
run;

In case there are duplicates and it is important to check what is the original input of each observation, it is possible to be done.

[stephcolter]

I already have them converted and in their own folder...as sas datasets so I don't need the first part...but the second part...This is going to merge all of the databases by the SEQN number? And keep all the data in each dataset? Every dataset will have the same SEQN number that's what they have in common I don't want those deleted

[Shmuel]

All converted files are saved in their original name in sasfile library.

Datasets TEMP and WANT, as defined in the my posted code, are created in the WORK library.

If you find that the dataset WANT is OK and fits your requirements, just copy it to sasfile library from the work library.

I suggest to sum number of observations copied from each original dataset and compare the summary to the number of WANT dataset observations.

[Shmuel]

Next solved post is very similar to yours.

Check it:

https://communities.sas.com/t5/SAS-Programming/Convert-XPT-to-SAS-Datasets/td-p/97872 

[stephcolter]

Let me clarify I do not need assistnce with making them sas datasets. I've already done that I just need all 6 six datsets MERGED by their common variable SEQN

[Kurt_Bremser]

I suggest that all further discussion takes place here: https://communities.sas.com/t5/SAS-Programming/Merge-Six-Sas-Datasets-Together/td-p/685136