topic Re: Appending two datasets in SAS Programming

Appending two datasets

wlierman — Thu, 17 Dec 2020 15:05:14 GMT

I have one dataset (Arias_View1)that I exported from Arias (At Risk Identification Alerting System) with 26,328 records and a second exported Arias dataset (Arias_View2) with 91,060 records. These datasets have the same number of columns and header names. The only difference is in the first dataset (Arias_View1)

These datasets need to be stacked vertically with Arias_View1

Arias View2

So I ran this code

Proc Sql noprint;
CREATE TABLE SASCDC_2.Arias_County_Race_Ethnicity AS
SELECT *
From SASCDC_2.ARIAS_View_1
OUTER UNION CORR
SELECT * 
FROM SASCDC_2.ARIAS_View_1;
quit;

But the error read that field 6 in the first dataset (Arias_View1) is of different form that field 6 in second data set Arias_View2. The field is DOB and in the first dataset and second dataset DOB is defined as follows:

DOB is defined as NUM length 8 format MMDDYY10. and in the second (Arias_View2) DOB is defined as text length 19 format $19. informat $19.

To try and correct the problem I ran

Data SASCDC_2.Arias_VIEW_2;

Set SASCDC_2.Arias_VIEW_2;

dobchar = put(DOB, $10.);

drop DOB;

rename dobchar = DOB;

run;

However, this creates a DOB variable that is Text with length 10.

The SQL query with Outer Union Corr does not execute because it notes that DOB is different in VIew1 than in View2.

How do I solve the DOB issue so that the Outer Union Corr runs successfully?

Thank you for your assistance again in this problem.

wlierman

Re: Appending two datasets

Kurt_Bremser — Thu, 17 Dec 2020 15:11:50 GMT

Which file format was used to transfer the data to SAS?

Re: Appending two datasets

wlierman — Thu, 17 Dec 2020 15:35:54 GMT

I export from the Arias platform (which is a Microsoft product that was developed for the contact tracing effort

of the covid response and recovery) and there is the old limit of 100,000 records to export into excel (I think base excel takes well over a million now). So I really don't know which file format was used - I think the numeric format.

Thank you.

wlierman

Re: Appending two datasets

tarheel13 — Thu, 17 Dec 2020 15:54:14 GMT

They will have to be the same data type. Did you also try proc append or data dsname; set ds1 ds2; run;?

Re: Appending two datasets

ballardw — Thu, 17 Dec 2020 16:03:22 GMT

@wlierman wrote:

I export from the Arias platform (which is a Microsoft product that was developed for the contact tracing effort

of the covid response and recovery) and there is the old limit of 100,000 records to export into excel (I think base excel takes well over a million now). So I really don't know which file format was used - I think the numeric format.

Thank you.

wlierman

Do they have an option to export to CSV (which is NOT Excel) or to TXT (which might be tab delimited?).

Excel is a lousy interchange platform because Excel is known to change some values when saving files.

And in at least one contractor I deal with they will create "templates" for writing out data that are either very poorly designed, corrupted or mangled by programmers such that I get values that are supposed to be "Annual income in dollars" that in the exported Excel file are formatted as Dates, personal identifiers and dates that are formatted as currency. Guess what happens when importing any of that into SAS? The incorrect type is used. So I have to manually change the columns in the spreadsheet to correct type before reading the data into SAS. (Which I do by saving to CSV and reading with a data step to help trap the other garbage they put in the files)

Re: Appending two datasets

Kurt_Bremser — Thu, 17 Dec 2020 16:17:49 GMT

What is the extension of the files you imported into SAS? Can they be viewed with a text editor (e.g. Windows Editor)? If yes, copy/paste a few lines (including the header) into a box opened with this button:

so we can show you a step that reads this data with consistent structure.

How do you run SAS? University Edition, On Demand for Academics, locally installed on your PC, or client/server with either Enterprise Guide or SAS Studio?

Re: Appending two datasets

wlierman — Thu, 17 Dec 2020 16:28:53 GMT

Hello,
Unfortunately, Microsoft provided just the bare bones - export to excel with the xlsx extension no CSV or txt. Arias still has bugs to work out and occasionally Power BI which is in the Microsoft stable.
wlierman

Re: Appending two datasets

wlierman — Thu, 17 Dec 2020 16:34:53 GMT

No I didn't use proc append. I considered it but Proc sql looked more straight forward.
How would the append coding look?

Re: Appending two datasets

ballardw — Thu, 17 Dec 2020 16:43:14 GMT

@wlierman wrote:
Hello,
Unfortunately, Microsoft provided just the bare bones - export to excel with the xlsx extension no CSV or txt. Arias still has bugs to work out and occasionally Power BI which is in the Microsoft stable.
wlierman

Understand. My limited experience with Power(less) BI has so far not been very encouraging and less so with stuff built on top of it. But that may be the result of low-bid government contractors...

Re: Appending two datasets

wlierman — Thu, 17 Dec 2020 17:04:53 GMT

Microsoft has its hands in a lot of different areas. Now I read that Guido von Rossum (Python developer) is working with MS.
What puzzles me on splitting the data and exporting as xlsx files into SAS is why does DOB come across as both numeric in part and text in the other part?
Is there a method that reads the two pieces (or more) consistently? I am also concerned that I may lose observations.
Thank you.
wlierman

Re: Appending two datasets

tarheel13 — Thu, 17 Dec 2020 17:05:47 GMT

You can do this but the problematic variable will have to be converted. it has to be the same type in both datasets or it throws errors. Either convert both to character or both to numeric.

data want;

set view1 view2;

run;

Proc append do something like:

proc append base=view1 data=view2;

run;

Re: Appending two datasets

Kurt_Bremser — Thu, 17 Dec 2020 17:05:54 GMT

Then you will have to fix the Excel imports, so that the column attributes (and contents) match. Convert the strings to dates.

Re: Appending two datasets

Kurt_Bremser — Thu, 17 Dec 2020 17:09:12 GMT

You could open all Excel files with Excel and save as csv, which you then read with a data step.

Re: Appending two datasets

wlierman — Thu, 17 Dec 2020 17:09:53 GMT

I will try that and let you know. Thanks.

Re: Appending two datasets

wlierman — Thu, 17 Dec 2020 18:37:42 GMT

Here is the notepad excerpt. There is a word wrap here

Last Name	First Name	DOB	Monitoring Status	Status	Address 1	City	County	Preferred Method of Contact
Cruz Nunez	Samuel	5/16/1992	Completed Monitoring	Inactive	"33685 NW Vadis Rd #13
Cornelius, OR 97113"	Cornelius	Washington	Phone
Cruz Nunez	Saul	1/29/1997	Completed Monitoring	Inactive	"33685 NW Vadis Rd #13
Cornelius, OR 97113"	Cornelius	Washington	Phone
Cruz Nunez	Armando	12/4/1988	Completed Monitoring	Inactive	"33685 NW Vadis Rd #13
Cornelius, OR 97113"	Cornelius	Washington	Phone
Garcia Zamora	Teresa	10/1/1992	Completed Monitoring	Inactive	"33685 NW Vadis Rd #13
Cornelius, OR 97113"	Cornelius	Washington	Phone
Garcia	Humberto	7/20/1994	Completed Monitoring	Inactive	"33685 NW Vadis Rd #13
Cornelius, OR 97113"	Cornelius	Washington	Phone
Garcia Carrada	Celso	4/6/1988	Completed Monitoring	Inactive	"33685 NW Vadis Rd #13
Cornelius, OR 97113"	Cornelius	Washington	Phone
Nunez Reyes	Bernardina	5/20/1977	Completed Monitoring	Inactive	"33685 NW Vadis Rd #13
Cornelius, OR 97113"	Cornelius	Washington	Phone
Estrada Dolores	Yessica	3/29/1990	Completed Monitoring	Inactive	"33685 NW Vadis Rd #17
Cornelius, OR 97113"	Cornelius	Washington	Phone
Garcia Zamora	Teresa	10/1/1992	Completed Monitoring	Inactive	"33685 NW Vadis Rd #13
Cornelius, OR 97113"	Cornelius	Washington	Phone

If you can help solve the problem of how to get the DOB format consistent between the two pieces Arias_view1 and rias_view2 it will be much appreciated.

Thanks.

wlierman

Re: Appending two datasets

tarheel13 — Thu, 17 Dec 2020 18:51:53 GMT

What is the desired format of DOB? You will have to use the conversion functions, either input or put to convert it to the same type. Once you do that you should be able to concatenate your datasets.

Re: Appending two datasets

tarheel13 — Thu, 17 Dec 2020 18:58:38 GMT

Please try this. First convert DOB to mmddyy10 in view2 then stack it together with view1 in a data step.

data view2(drop=tempvar);

set SASCDC_2.Arias_VIEW_2(rename=(dob=tempvar));

dob = input(tempvar, mmddyy10.);

run;

data want;

set SASCDC_2.Arias_VIEW_1 view2;

run;

Re: Appending two datasets

wlierman — Thu, 17 Dec 2020 19:15:52 GMT

Hello,

Your coding worked in that the two datasets stacked vertically and the DOB - numeric and text - issue has been solved.

Thank you very much.

I want to recommend your method as a Solution but don't see where I can - because I had already designated a solution but your answer is complete. I will indicate that this is a Solution, if you could direct me to how to do so.

Thank you again for this help.

wlierman