topic Combining/Merging Three datasets in SAS Procedures

Combining/Merging Three datasets

deleted_user — Sun, 14 Feb 2010 06:02:55 GMT

Hello ,

could someone please help me out.I've sales data with variables Date of Sale, Number of units sold and the Laptop model ( there are two A and B ).I've three raw data files called :
Period 1 sales , Period 2 Sales_A, Period 2 Sales_B.

I used the following code to first obtain the raw data in proper format :

libname sc 'h:\SAScourse';
data Period1Sales;
infile 'h:\SAScourse\period1_sales_data.dat';
input TransactionID $1-8
DateofSale $9-18
TimeofSale $19-26
LaptopModel $27-32
UnitsSold 33-35
Warranty 36;
proc print data = Period1Sales;
TITLE 'period1_sales_data.dat';
RUN;

data period2_sales_data_A;
infile 'h:\SAScourse\period2_sales_data_A.dat';
Input TransactionID $1-8
DateofSale $9-18
LaptopModel $19-24
UnitsSold 25-27
Warranty 28;
proc print data=period2_sales_data_A;
TITLE 'period2_sales_data_A';
RUN;

data period2_sales_data_B;
infile 'h:\SAScourse\period2_sales_data_B.dat';
Input TransactionID $1-8
TimeofSale $9-16;

proc print data=period2_sales_data_B;
TITLE 'period2_sales_data_B';
RUN;

There was no problems with the first and third codes , but the 2nd code for the raw data file period2_sales_data_A is showing 0 obseravations selected . I get the varables but with no entries in them.

Also , is there a way to combine/merge all three raw data files into one file to analyse the data.

Regards ,
mark

Re: Combining/Merging Three datasets

sbb — Sun, 14 Feb 2010 07:05:15 GMT

To diagnose your input data problem, you may consider adding the SAS DATA step statement below to display to your SAS log:

PUTLOG _ALL_;

..or..

PUTLOG _INFILE_;

Also, to combine your SAS files for analysis, again, a DATA step approach using either a SET statement or depending on your needs, possibly a MERGE statement (with a companion BY statement) may be suitable.

Scott Barry
SBBWorks, Inc.

Re: Combining/Merging Three datasets

deleted_user — Mon, 15 Feb 2010 06:14:28 GMT

Hi Mark,

Here you can check that the numeric values are at the right positions as you mentioned in the second code:
UnitsSold 25-27
Warranty 28
Since other variables are character variables there will be no problem importing the file for them. But if you come across a character on the above mentioned positions, the values will not be taken, So you can check the period 2 file once again to check those positions. 25-27 & 28. If that is the case, then it would generate an error in the log and as Scott mentioned you need to go through the log for the same.

Thanks,
Saurabh.

Re: Combining/Merging Three datasets

deleted_user — Thu, 18 Feb 2010 01:19:47 GMT

Hi Saurabh ,

I'd really be much greatful if you could sugggest an appropriate code for this as i'm just a beginner in the SAS . I'd really appreciate that .

Thanks and kind reagards ,

mark

Re: Combining/Merging Three datasets

deleted_user — Thu, 18 Feb 2010 06:15:31 GMT

Hi Mark,

Well as I had told that there might be a issue of different datatypes while creating the datasets.
I can help you if you can post a sample data which you are working on.
One thing which you can watch out for is that if your numeric data like Units sold contain commas(eg: 1,000) or a simple numeric value(1000).
If it contains comma then try importing it with a informat comma. else it will contradict with the simple numeric format and will try to import it as character value, which would give an error.

If you still find problems then request you to post a sample data which can give us some insight on why you might be facing the problem.

Thanks,
Saurabh.

Re: Combining/Merging Three datasets

deleted_user — Thu, 18 Feb 2010 10:46:50 GMT

Hello Saurabh ,

I couldnt find a link to attach the sample data.Could you please suggest

Kind regards ,
mark

Hi Mark,
>
> Well as I had told that there might be a issue of
> different datatypes while creating the datasets.
> I can help you if you can post a sample data which
> you are working on.
> One thing which you can watch out for is that if your
> numeric data like Units sold contain commas(eg:
> 1,000) or a simple numeric value(1000).
> If it contains comma then try importing it with a
> informat comma. else it will contradict with the
> simple numeric format and will try to import it as
> character value, which would give an error.
>
> If you still find problems then request you to post a
> sample data which can give us some insight on why you
> might be facing the problem.
>
> Thanks,
> Saurabh.

Re: Combining/Merging Three datasets

deleted_user — Thu, 18 Feb 2010 11:18:56 GMT

Hello Saurabh ,

The data is as follows :

Period 2A ( first week ) , Period2B ( second week ) for the period 15/11/2009 – 28/11/2009 :

Period 2A

Transaction Laptop Units
Obs ID DateofSale Model Sold Warranty

1 RX000309 15/11/2009 AP3965 1 0
2 RX000310 15/11/2009 AP3965 1 0
3 RX000311 15/11/2009 AP3965 1 0
4 RX000312 15/11/2009 AP3965 1 0
5 RX000314 15/11/2009 AP3965 1 0
6 RX000317 15/11/2009 AP3965 1 0
7 RX000318 15/11/2009 AP3965 1 0
8 RX000319 15/11/2009 AP3965 1 0
9 RX000320 15/11/2009 AP3965 1 0
10 RX000326 15/11/2009 AT3600 1 0

Period 2B

Transaction Timeof
Obs ID Sale

1 RX000551 18:21:58
2 RX000550 14:01:20
3 RX000549 14:38:43
4 RX000548 09:39:52
5 RX000547 11:30:21
6 RX000546 11:31:28
7 RX000545 10:21:58
8 RX000544 10:34:57
9 RX000543 04:00:04
10 RX000542 10:14:31

Re: Combining/Merging Three datasets

deleted_user — Tue, 23 Feb 2010 06:43:44 GMT

Hi Mark,

If the problem still persists,
I would like to tell you that the positions which you are using in the code are not correct :
TransactionID $1-8
DateofSale $9-18
LaptopModel $19-24
UnitsSold 25-27
Warranty 28;

As per the data, count spaces also as a part which constitutes one position.

then retry using the updated positions.
Example: 1-8 is a transaction id.
then 9th position is a space in the data and not the starting of the date value. so you face this problem.
date starts from 10th position.

I hope this would clear the doubt, and will help you solve the problem.

Thanks,
Saurabh.