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
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.
im not trying to import they are already sas7bdat files now... i just need to merge the four datasets by SEQN
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.
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.
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
I suggest that all further discussion takes place here: https://communities.sas.com/t5/SAS-Programming/Merge-Six-Sas-Datasets-Together/td-p/685136
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: