topic Re: Merging Dataset in Statistical Procedures

Merging Dataset

genius99 — Tue, 10 Sep 2019 17:59:24 GMT

I am trying to merge datasets, and this is my code.

/*Load Data*/
data Employees;
    infile "C:/Users/100355202/Desktop/employees.csv" dsd dlm = "09"x;
    input EmployeeID LastName :$20. FirstName :$20. Title :$20. Gender :$20. BirthDate :mmddyy10. 
          HireDate :mmddyy10. Address :$20. City :$20. Region :$20. PostalCode :$20. Country :$20. 
          HomePhone :$20. Extension :$20. ReportsTo;
run;
proc import datafile="C:/Users/100355202/Desktop/employees.csv"  out=employees dbms=csv replace;
         getnames=yes;
run;
/* Merge dataset */
data Employees_Orders;
   merge Employees Orders;
   by EmployeeID;
run;

the orders code is like this

/*Load Data*/
data Orders;
    infile "C:/Users/100355202/Desktop/Orders.txt" dsd dlm = "09"x firstobs = 2;
    input OrderID CustomerID :$20. EmployeeID :$20. OrderDate :mmddyy10. 
          RequiredDate :mmddyy10. ShippedDate :mmddyy10. ShipVia Freight 
          ShipName :$20. ShipAddress :$20. ShipCity :$20. ShipRegion :$20. 
          ShipPostalCode :$20. ShipCountry :$20. ;
run;

and I am getting these notes

can anyone help me solve this

Re: Merging Dataset

Jagadishkatam — Tue, 10 Sep 2019 18:27:38 GMT

If you run the below code after reading the data files then you should not get the error what you posted

/* Merge dataset */
data Employees_Orders;
   merge Employees Orders;
   by EmployeeID;
run;

Re: Merging Dataset

genius99 — Tue, 10 Sep 2019 18:51:30 GMT

I tried to sort them first,

/*Load Employees*/
data Employees;
    infile "C:/Users/100355202/Desktop/employees.csv" dsd dlm = "09"x;
    input EmployeeID LastName :$20. FirstName :$20. Title :$20. Gender :$20. BirthDate :mmddyy10. 
          HireDate :mmddyy10. Address :$20. City :$20. Region :$20. PostalCode :$20. Country :$20. 
          HomePhone :$20. Extension :$20. ReportsTo;

proc import datafile="C:/Users/100355202/Desktop/employees.csv"  out=employees dbms=csv replace;
         getnames=yes;
run;
/*Load Orders*/
data Orders;
    infile "C:/Users/100355202/Desktop/Orders(1).txt" dsd dlm = "09"x firstobs = 2;
    input OrderID CustomerID :$20. EmployeeID OrderDate :mmddyy10. 
          RequiredDate :mmddyy10. ShippedDate :mmddyy10. ShipVia Freight 
          ShipName :$20. ShipAddress :$20. ShipCity :$20. ShipRegion :$20. 
          ShipPostalCode :$20. ShipCountry :$20. ;
run;

/*Sort Employees*/
data Employees;
    proc sort employees;
    by EmployeeID;
run;

/*Sort orders*/
data orders;
    Proc sort orders;
    by EmployeeID;
run;

/* Merge dataset */
data Employees_Orders;
   merge Employees Orders;
   by EmployeeID;
run;

and now I have this note

Re: Merging Dataset

Jagadishkatam — Tue, 10 Sep 2019 18:53:38 GMT

Please try the below code

/*Sort Employees*/
    proc sort data=employees;
    by EmployeeID;
run;

/*Sort orders*/
    Proc sort data=orders;
    by EmployeeID;
run;

/* Merge dataset */
data Employees_Orders;
   merge Employees Orders;
   by EmployeeID;
run;

Re: Merging Dataset

genius99 — Tue, 10 Sep 2019 19:00:30 GMT

it's not working out still,

Re: Merging Dataset

Jagadishkatam — Tue, 10 Sep 2019 19:12:17 GMT

Could you please check in the dataset what is exact name of employeeid may be this variable name is not right

Re: Merging Dataset

genius99 — Tue, 10 Sep 2019 19:19:15 GMT

the name is still employeeid

Re: Merging Dataset

Jagadishkatam — Tue, 10 Sep 2019 19:28:34 GMT

ok but seems like it does not have the employee id, the employeeid variable have the dates, are you sure it is correct.

Re: Merging Dataset

ballardw — Tue, 10 Sep 2019 19:38:41 GMT

@genius99 wrote:

I am trying to merge datasets, and this is my code.

/*Load Data*/
data Employees;
    infile "C:/Users/100355202/Desktop/employees.csv" dsd dlm = "09"x;
    input EmployeeID LastName :$20. FirstName :$20. Title :$20. Gender :$20. BirthDate :mmddyy10. 
          HireDate :mmddyy10. Address :$20. City :$20. Region :$20. PostalCode :$20. Country :$20. 
          HomePhone :$20. Extension :$20. ReportsTo;
run;
proc import datafile="C:/Users/100355202/Desktop/employees.csv"  out=employees dbms=csv replace;
         getnames=yes;
run;
/* Merge dataset */
data Employees_Orders;
   merge Employees Orders;
   by EmployeeID;
run;

the orders code is like this

/*Load Data*/
data Orders;
    infile "C:/Users/100355202/Desktop/Orders.txt" dsd dlm = "09"x firstobs = 2;
    input OrderID CustomerID :$20. EmployeeID :$20. OrderDate :mmddyy10. 
          RequiredDate :mmddyy10. ShippedDate :mmddyy10. ShipVia Freight 
          ShipName :$20. ShipAddress :$20. ShipCity :$20. ShipRegion :$20. 
          ShipPostalCode :$20. ShipCountry :$20. ;
run;

and I am getting these notes

can anyone help me solve this

Cause of the first error. The syntax to reference a data set is Library.Dataset one and only one period is allowed.

(Actually a one of the SAS products does allow syntax with more periods but in that case ORDERS would be some sort of member of Work.Employees such as a variable in a data set)

You don't show the log from running your data step creating the Orders dataset. HOWEVER the merge wouldn't work any way.

In your employees data set employeeid is NUMERIC:

input EmployeeID LastName :$20.

reads it as numeric.

In your Orders data step:

input OrderID CustomerID :$20. EmployeeID :$20.

reads EmployeeId as a character variable.

But with the error of work.orders does not exist makes me suspect that either the data stepping reading the data failed and did not create the data set or was not actually submitted.