Appending two datasets

[wlierman]

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

[Kurt_Bremser]

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

[wlierman]

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

[tarheel13]

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

[wlierman]

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

[tarheel13]

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;

[wlierman]

I will try that and let you know.  Thanks.

[ballardw]

---

@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)

[wlierman]

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

[ballardw]

---

@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...

[wlierman]

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

[Kurt_Bremser]

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

[Kurt_Bremser]

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

[Kurt_Bremser · Accepted solution]

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?

[wlierman]

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