BookmarkSubscribeRSS Feed
genius99
Calcite | Level 5

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

merge.PNG

can anyone help me solve this

8 REPLIES 8
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
genius99
Calcite | Level 5

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

sort.PNG

Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
genius99
Calcite | Level 5

it's not working out still,

not.PNGcode.PNG

Jagadishkatam
Amethyst | Level 16
Could you please check in the dataset what is exact name of employeeid may be this variable name is not right
Thanks,
Jag
genius99
Calcite | Level 5

the name is still employeeid

id.PNG

Jagadishkatam
Amethyst | Level 16
ok but seems like it does not have the employee id, the employeeid variable have the dates, are you sure it is correct.
Thanks,
Jag
ballardw
Super User

@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

merge.PNG

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 803 views
  • 0 likes
  • 3 in conversation