SAS Merging and exporting dataset in Excel

 SAS : STATISTICAL ANALYTICAL SOFTWARE 

In this blog, I have talked about merging of dataset with the help of example and introduction to interface of SAS software.

There are five windows in SAS software:

Results : represents printing procedures when we print any dataset or report in a tree-like diagram.

Explorer: consists of active libraries, gives access to files present on PC.

Output: while printing any report or displaying means in dataset this window shows the way our report will look like after printing.

Log:   good programming practice: "always check log window after submitting SAS        statements to check for any errors in our coding".

Editor: window where we code SAS statements.

Results to editor windows are arranged in left to right direction.

 The layout after this quick introduction will be task, code, output.

One to one merging 

First dataset is merged with second dataset by a common variable that is common in both datasets.

• In SAS whenever you merge two datasets, both the datasets should be sorted by  common variable.

Chocolate pyramid

Task:

I have two datasets. First dataset is about number of pieces of that particular chocolate code sold on that day. And I have provided this dataset in a notepad file data1.txt (notice each observation is separated by a comma as a delimiter just like in csv file!)

Second dataset data2.txt file consist of that chocolate code number and description about it.

In both the datasets comma is used as a delimiter to separate each observation within a record. I want you to combine these two datasets into one dataset and deliver it in excel file using SAS.

Code:

/*first import dataset and make a SAS dataset out of it*/

data work.dataset1;   /* name of the SAS dataset consisting data1 records*/

infile "D:\data1.txt" dlm=",";  /* refer to the file path and mention the comma as a delimiter*/

input codeno $ PS;  /*input statement creates columns in which SAS stores the observations*/

label codeno = "code number of that chocolate";  /*once column is made label it for reference purpose*/

label  PS = "peices of chocolate sold that day";

run; /*submit the above SAS statements and generate dataset1*/

Log and Dataset1

Log 

Dataset1 

data work.dataset2;  /* name of SAS dataset consist of records of data2 file*/

infile "D:\data2.txt" dlm=","; /*file path and delimiter as comma*/

length describe $61.;   /*length of records (number of characters) in column "describe"*/

input codeno $ describe $;  /* input statement creates codeno and describe column*/

label codeno = "code number of that chocolate"; /*labels to created columns*/

label describe = "description of chocolate item";

run;

Dataset2

proc sort data = work.dataset1 out = data1_sort; /*sort the dataset1 by common variable codeno*/

by codeno; /*by statement common variable codeno*/

run;

/*now good programming practice never sort the raw dataset rather make new sorted dataset so that you retain the original SAS dataset*/

/* out = statement makes new sorted SAS dataset*/

proc sort data= work.dataset2 out = data2_sort;

by codeno;

run;

Sorted dataset1

Sorted dataset2

Now we are performing one to one merge stay tune to know why we are doing one to one merge.

data onetoone; /* merged SAS dataset name that is created in work library*/

merge data1_sort  data2_sort;    /* merge statement merge data-set-1 data-set-2*/

by codeno;  /*by statement merging by common variable codeno*/

run;

One to one merged dataset

Why this is one to one merge as we are merging one observation from first dataset to only single observation to another dataset.

PROC EXPORT DATA=onetoone outfile="D:\sales_data.xlsx" 

dbms= xlsx replace; 

run;

/*above code to export SAS dataset in Excel sheet*/

Output:

Codeno: Code number of that chocolate item.
PS: number of pieces sold that day
describe: description of that chocolate product

Here, the code "M315" does not have any value in "PS" column (number of pieces sold that day)

• Perks:
• INPUT statement character-variable $  numeric variable ;
• End the SAS statement with semicolon.
• /* .................*/   to specify comments in SAS

Very end of any SAS statement put ";" .

• Proc statements procedure and data steps are dataset statements in SAS.
• To view created SAS dataset click on explorer window then select work library under Active libraries and click in the dataset icon. 

That's all, in next blog we will look for one more type of merging in SAS software.

You have SAS On Demand Academics which provides online SAS programming, sign-in create account and start practicing!

Keep exploring more about SAS programming 😊