topic Re: Combining Data Sets in SAS Studio

Combining Data Sets

marianhabesland — Wed, 08 Nov 2017 15:58:21 GMT

I am trying to combine three data sets: Src_iowaresidents, Src_ms_citizens and Src_utah_records into one data set called Work.Contacts. Each data set by itself is working fine, but when I combine them together something goes wrong. Each data set looks like this:

PS. Sorry parts of it are in all caps. My professor prefers it that way.

OPTIONS FMTSEARCH = (HypTabs.HypFormats WORK LIBRARY);

DATA WORK.Contact_IA;
RETAIN SSN Inits City StateCd ZipCd;
SET HypTabs.Src_IowaResidents (RENAME =(ZipCd = ZipCdSrc));

Inits = SUBSTR(Initials, 3, 2)||SUBSTR(Initials, 1, 1);
City = PROPCASE(City);
StateCd = "IA";

ZipCd = PUT(ZipCdSrc, 5.);

KEEP SSN Inits City StateCd ZipCd;

LABEL SSN = "Social Security Number"
Inits = "Subject Initials"
City = "City"
StateCd = "State Code"
ZipCd = "Zip Code";

FORMAT StateCd $StateCd.;
RUN;

PROC SORT;
BY SSN;
RUN;

DATA WORK.Contact_MS;
RETAIN SSN Inits City StateCd ZipCd;
SET HypTabs.Src_MS_Citizens (RENAME =(SocSecNum = SSN));
Inits = COMPRESS(CATS(FirstInit, MiddleInit, LastInit), '.');
City = SCAN(CityState, 1, ',');
StateCd = "MS";

KEEP SSN Inits City StateCd ZipCd;

LABEL SSN = "Social Security Number"
Inits = "Subject Initials"
City = "City"
StateCd = "State Code"
ZipCd = "Zip Code";
FORMAT StateCd $StateCd.;
RUN;

PROC SORT;
BY SSN;
RUN;

DATA WORK.Contact_UT;
RETAIN SSN Inits City StateCd ZipCd;
SET HypTabs.Src_UT_Records ( RENAME = ( Inits = InitsTemp
ZipCode = ZipCd )
);

SSN1 = INPUT(ID, 12.);
SSN = PUT(SSN1, SSN11.);

Inits = COMPRESS(InitsTemp, '.');

City = SCAN(CitySt, 1, ',');

StateCd = "UT";

KEEP SSN Inits City StateCd ZipCd;

LABEL SSN = "Social Security Number"
Inits = "Subject Initials"
City = "City"
StateCd = "State Code"
ZipCd = "Zip Code";
FORMAT StateCd $StateCd.;
RUN;

PROC SORT;
BY SSN;
RUN;

DATA WORK.Contact (LABEL = "Contact Information");
SET HypTabs.Src_IowaResidents
HypTabs.Src_MS_Citizens
HypTabs.Src_UT_Records;
KEEP SSN Inits City StateCd ZipCd;
RUN;

Re: Combining Data Sets

Reeza — Wed, 08 Nov 2017 04:11:17 GMT

@marianhabesland wrote:

I am trying to combine three data sets: Src_iowaresidents, Src_ms_citizens and Src_utah_records into one data set called Work.Contacts. Each data set by itself is working fine, but when I combine them together something goes wrong.

It helps if you show what you tried and what went wrong.

Nothing shown in the code here combines data and you don’t say what’s 'wrong'.

Re: Combining Data Sets

marianhabesland — Wed, 08 Nov 2017 04:22:35 GMT

Sorry about that. Just realized part of the code got cut off. It is now marked in red. As for what goes wrong the data set WORK.Contacts does not even show up in the output. The only error message I get is

ERROR: Variable ZipCd has been defined as both character and numeric.

This does not really make sense to me because when I look at the data sets ZipCd is always a character variable.

Re: Combining Data Sets

Shmuel — Wed, 08 Nov 2017 04:37:55 GMT

Run proc contents with each of the 3 datasets and check type of ZIPCD.

You will find which are numeric and which are not.

The given code itself is not enough to guess the type.

Re: Combining Data Sets

ballardw — Wed, 08 Nov 2017 15:23:46 GMT

PLEASE edit your post to remove any displayed SSN. I may be paranoid about such things but better safe than sorry.

Initials + residence+ zip code and SSN could compromise an individual's SSN for exploitation.

Re: Combining Data Sets

marianhabesland — Wed, 08 Nov 2017 15:46:41 GMT

I totally understand your concern. This is however 100% fictional data that was made up for academic use. I’ll make sure to edit it though so no one else gets worried.

Re: Combining Data Sets

Reeza — Wed, 08 Nov 2017 15:58:06 GMT

@ballardw I sort of assumed with the word professor there it would be fake data...but they are valid SSNs and do map to people. And it's also weird that US even has online systems that show me that....

Re: Combining Data Sets

marianhabesland — Wed, 08 Nov 2017 16:20:08 GMT

Thanks for your reply. I have checked and it is a character variable in all three data sets. I have even put in a statement to ensure this. This is the full message I get if that gives any more clues to the problem:

NOTE: Data file HYPTABS.SRC_IOWARESIDENTS.DATA is in a format that is native to another host, or the file encoding does not match

the session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might

reduce performance.

NOTE: Data file HYPTABS.SRC_MS_CITIZENS.DATA is in a format that is native to another host, or the file encoding does not match the

session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce

performance.

ERROR: Variable ZipCd has been defined as both character and numeric.

NOTE: Data file HYPTABS.SRC_UT_RECORDS.DATA is in a format that is native to another host, or the file encoding does not match the session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce

performance.

Re: Combining Data Sets

Reeza — Wed, 08 Nov 2017 16:28:29 GMT

ERROR: Variable ZipCd has been defined as both character and numeric.

Post the proc contents output from the three runs please.

Re: Combining Data Sets

marianhabesland — Wed, 08 Nov 2017 17:42:55 GMT

The CONTENTS Procedure

HYPTABS.SRC_IOWARESIDENTS	196
DATA	9
V9	0
11:17:29 15.09.2017	80
11:17:29 15.09.2017	0
	NO
	NO

WINDOWS_64
wlatin1 Western (Windows)

Engine/Host Dependent InformationData Set Page SizeNumber of Data Set PagesFirst Data PageMax Obs per PageObs in First Data PageNumber of Data Set RepairsExtendObsCounterFilenameRelease CreatedHost CreatedInode NumberAccess PermissionOwner NameFile SizeFile Size (bytes)

65536
1
1
817
196
0
YES
/folders/myfolders/BIOS6680/Hypertension Study/1 - Data/2 - Tabulations/src_iowaresidents.sas7bdat
9.0401M2
X64_8PRO
77
rwxrwx---
root
128KB
131072

Alphabetic List of Variables and Attributes# Variable Type Len937281645

BirthDt	Num	8
City	Char	20
Ethnicity	Char	12
Initials	Char	4
Race	Char	5
SSN	Char	11
Sex	Char	6
State	Char	4
ZipCd	Num	8

The CONTENTS Procedure

Data Set NameObservationsMember TypeVariablesEngineIndexesCreatedObservation LengthLast ModifiedDeleted ObservationsProtectionCompressedData Set TypeSortedLabel Data Representation Encoding

HYPTABS.SRC_MS_CITIZENS	207
DATA	10
V9	0
12:46:45 15.09.2017	110
12:46:45 15.09.2017	0
	NO
	NO

WINDOWS_64
wlatin1 Western (Windows)

65536
1
1
594
207
0
YES
/folders/myfolders/BIOS6680/Hypertension Study/1 - Data/2 - Tabulations/src_ms_citizens.sas7bdat
9.0401M2
X64_8PRO
82
rwxrwx---
root
128KB
131072

Alphabetic List of Variables and Attributes# Variable Type Len51082743916

CityState	Char	35
DOB	Char	9
Eth	Char	22
FirstInit	Char	2
Gender	Char	6
LastInit	Char	2
MiddleInit	Char	2
Racial	Char	16
SocSecNum	Char	11
ZipCd	Char	5

The CONTENTS Procedure

Data Set NameObservationsMember TypeVariablesEngineIndexesCreatedObservation LengthLast ModifiedDeleted ObservationsProtectionCompressedData Set TypeSortedLabel Data Representation Encoding

HYPTABS.SRC_UT_RECORDS	195
DATA	10
V9	0
09:08:11 15.09.2017	72
09:08:11 15.09.2017	0
	NO
	NO

WINDOWS_64
wlatin1 Western (Windows)

65536
1
1
908
195
0
YES
/folders/myfolders/BIOS6680/Hypertension Study/1 - Data/2 - Tabulations/src_ut_records.sas7bdat
9.0401M2
X64_8PRO
83
rwxrwx---
root
128KB
131072

Alphabetic List of Variables and Attributes# Variable Type Len10983651274

BirthDay	Num	8
BirthMonth	Num	8
BirthYear	Num	8
CitySt	Char	24
EthnicityCode	Char	1
GenderCode	Char	1
ID	Num	8
Inits	Char	6
RaceCode	Char	1
ZipCode	Char	5

So I can tell from the PROC CONTENTS that ZipCd is a numerical variable in the Src_iowaresidents data set. In my output it is however a character variable as I define that in my code. Is there someway I can fix this so that it becomes a character variable so that I can combine the datasets?

Re: Combining Data Sets

Shmuel — Wed, 08 Nov 2017 19:19:49 GMT

Add to your code, starting with:

DATA WORK.Contact_IA;
RETAIN SSN Inits City StateCd ZipCd;
SET HypTabs.Src_IowaResidents (RENAME =(ZipCd = ZipCdSrc));

a line:

DATA WORK.Contact_IA;
LENGTH zipcd $5;       /*** line to add ***/
RETAIN SSN Inits City StateCd ZipCd;
SET HypTabs.Src_IowaResidents (RENAME =(ZipCd = ZipCdSrc));

I hope it will sove your problem.

Re: Combining Data Sets

Shmuel — Wed, 08 Nov 2017 19:26:31 GMT

You created 3 datasets named with prefix: work.contact_

but you use the original source datasets:

DATA WORK.Contact (LABEL = "Contact Information");
SET HypTabs.Src_IowaResidents
HypTabs.Src_MS_Citizens
HypTabs.Src_UT_Records;
KEEP SSN Inits City StateCd ZipCd;
RUN;

Replace the 3 input datasets to those you created .

Re: Combining Data Sets

marianhabesland — Wed, 08 Nov 2017 21:20:14 GMT

Yes, that makes sense. Silly mistake. Thank you!!