How to combine two SAS data set (table) files to become one sas data set table

[Kayla_Tan222]

Hi,

Would like to ask how to combine two SAS data sets table to become one sas data sets table. For Ex,

TABLE 1

FRUITS        PRICE

ORANGE        5

BANANA        15

APPLE           5

TABLE 2

FRUITS                    PRICE

KIWI                             7

WATERMELON          20

PEAR                           9

i want to combine this two table ( i already save this two table as intermediary in SAS data set type in my local pc, screenshot is attached with this post) to become table like this without mess up the sequence

TABLE 3

FRUITS                    PRICE

ORANGE                     5

BANANA                     15

APPLE                        5

KIWI                             7

WATERMELON          20

PEAR                           9

can I use two proc import to import the two data sets table? or there is another way to do it?

[ScottBass]

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000173782.htm

Your answer is in one of the examples, but I recommend you read the entire document.

[heffo]

If you already have the tables as data set in SAS you can just do it this way: 

data Table3;
    set Table1 Table2;
run;

SAS will then read all the rows from the first data set, in the order that they are in that data set. Then when that is done, start with the next data set (and so on, if you have more data sets). 

Be aware of the fact that you might have different lengths of the variables and then the first tables lengths will be the one that SAS will use. This could then lead to truncation of the data. 

To get around this, you might want to add a length statement before the set statement. EG

data Table3;
    length Fruits $ 30 Price 8;
    set Table1 Table2;
run;

But the link thay Kayla sent was correct. 

If the data is outside of SAS then you will have to import them one by one first and then you can combine them. 

[Kayla_Tan222]

Hi heffo,

First, thanks for you reply.  I'm a very beginner , hope can learn more from you.

I have save my two table in type SAS data set in my local pc (shown in screenshot attached),

i have tried your method, but it keep saying:

ERROR: File WORK.POLA_CLM_AGG_201811.DATA does not exist.

ERROR: File WORK.POLA_CLM_MTH_201812.DATA does not exist.

Here's my code using your method

data Table3;

set pola_clm_agg_201811 pola_clm_mth_201812;

run;

I did not run any code before these code. I type this in new SAS program.

Is this means that i need to import the two SAS data set table before i combine them?

[Reeza]

You need to tell SAS where they are. Sounds like it’s not in the work library so you need to assign a library to where the files are located.

This video explains it around 4:00 minute mark. You should watch it all though.

https://video.sas.com/detail/videos/sas-analytics-u/video/4664335810001/accessing-your-existing-data...

[Reeza]

It depends. 

If these are from text files you can read them all in at once. 

Or you can import them individually and append them. This type of action is called an APPEND, which adds rows. 

Merges can add columns primarily but rows as well. 

PROC APPEND will stack data - this is a more efficient choice because it’s the fastest but that won’t matter if your tables are small. 

---

@Kayla_Tan222 wrote:

Hi,

Would like to ask how to combine two SAS data sets table to become one sas data sets table. For Ex,

TABLE 1

FRUITS        PRICE

ORANGE        5

BANANA        15

APPLE           5

TABLE 2

FRUITS                    PRICE

KIWI                             7

WATERMELON          20

PEAR                           9

i want to combine this two table ( i already save this two table as intermediary in SAS data set type in my local pc, screenshot is attached with this post) to become table like this without mess up the sequence

TABLE 3

FRUITS                    PRICE

ORANGE                     5

BANANA                     15

APPLE                        5

KIWI                             7

WATERMELON          20

PEAR                           9

can I use two proc import to import the two data sets table? or there is another way to do it?

---

[Kayla_Tan222]

Hi Reeza,

you mean i need to use proc import twice to import the two sas data set table and then use proc append to combine them?

The code i save the table as intermediary is below:

　

libname polaclm '\\kaiwksgh415thw5\Data\POLA\2018\201811\DBF';

data polaclm.POLA_clm_agg_201811;

proc import datafile = '\\kaiwksgh415thw5\Data\POLA\2018\201811\DBF\POLA_clm_agg_201811.csv'

out= polaclm.POLA_clm_agg_201811 REPLACE

dbms = csv

;

　

run;

However, if i want to import the file in SAS data set type, what i need to put for the dbms?

[Reeza]

You don’t need to import it, you can just reference it once you have the libname assigned. The video explains it in detail and I’ve told you what time to jump to if you don’t want to watch it all. It explains how a SAS stores and references files.

[heffo · Accepted solution]

*This libname statement is only needed if you want to write the data set to that folder.
You migth use WORK library instead;
libname polaclm '\\kaiwksgh415thw5\Data\POLA\2018\201811\DBF';

/*data polaclm.POLA_clm_agg_201811;*/ *This one does nothing.;
/*You import the two CSV files and create two different SAS data sets.*/
proc import datafile = '\\kaiwksgh415thw5\Data\POLA\2018\201811\DBF\POLA_clm_agg_201811.csv'
			out= polaclm.POLA_clm_agg_201811 /*or type in work.POLA_clm_agg_201811 or just POLA_clm_agg_201811*/
			REPLACE
			dbms = csv;
run;

proc import datafile = '\\kaiwksgh415thw5\Data\POLA\2018\201811\DBF\POLA_clm_agg_201812.csv'
			out= polaclm.POLA_clm_agg_201812 /*or type in work.POLA_clm_agg_201812 or just POLA_clm_agg_201812*/
			REPLACE
			dbms = csv;
run;

/*Now you combine the data sets and creates a new data set in the WORK library*/
data Table3;
	set pola_clm_agg_201811 
		pola_clm_mth_201812;
run;

[Kayla_Tan222]

Hi heffo,

actually my two files are not in csv format. its in sas data set format. However there will be an error if I put dbms=sas.

cant I put dbms=sas? or my code for this line is wrong?

[heffo]

In your code you referred to the csv files in the import. So, that is what I assumed. If they are in SAS tables and stored in the folder '\\kaiwksgh415thw5\Data\POLA\2018\201811\DBF' then it will be enough to use the libname statement. 

*Create a libname in this session so that you can read the SAS tables from where they are stored;
libname polaclm '\\kaiwksgh415thw5\Data\POLA\2018\201811\DBF';

/*Now you combine the data sets and creates a new data set in the WORK library*/
data Table3;
	set polaclm.pola_clm_agg_201811 
		polaclm.pola_clm_mth_201812;
run;
*Clear/remove the libname. It will happen automatically when you close your session, but it is a good thing to do. ;
libname polaclm clear;