Help using Base SAS procedures

how to import excel with headings

Reply
Frequent Contributor
Posts: 111

how to import excel with headings

Good day all.

I am importing excel file. it includes information of employee and student.

I know how to import this data into sas. But what my requirement is that along with  heading also there in dataset. Here the variable eno is numeric. and std_id is numeric.  I mention resultant output in sheet1.

Is there like this output is possible in sas.

Your valid comments are welcome.

Thanks in advance.

The data is like this.

Employee Details on 10/03/2015
eno ename salary
1aaa1000
2bbb2000
3ccc3000
4ddd4000
5eee5000
6aaa6000
7bbb7000
8ccc8000
9ddd9000
10eee10000
Student Details on 10/03/2015
std_idstd_namemarks
101aaa50
102bbb60
103ccc95
104ddd82
105eee96
106aaa74
107bbb52
108ccc38
109ddd40
110eee64

the output is like this.

Employee Details on 10/03/2015
eno ename salary
1aaa1000
2bbb2000
3ccc3000
4ddd4000
5eee5000
6aaa6000
7bbb7000
8ccc8000
9ddd9000
10eee10000
Student Details on 10/03/2015
101aaa50
102bbb60
103ccc95
104ddd82
105eee96
106aaa74
107bbb52
108ccc38
109ddd40
110eee64
Super User
Posts: 7,118

Re: how to import excel with headings

What you need looks just like 2 PROC PRINTs with a corresponding TITLE statement before each.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 111

Re: how to import excel with headings

proc print is not possible. i need the output in data step.

i know how to import it but with including column heading how it is done.

this both data exists in one sheet only.

I need the output like

first column heading after data.

Super User
Posts: 7,118

Re: how to import excel with headings

You either have a dataset or printed output, you can't mix both.

How would you spread "Employee Details on 10/03/2015" across the three columns? Or would you want to abandon the separate columns and have everything in one variable called "output_line"?

What would be the use of such a dataset? (None, IMO)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 111

Re: how to import excel with headings

There is a logic behind this.

In one excel sheet different data is available. But variables are same but.records are updating.

So at the end we unable to identify which data is belongs to whom. So i mention one heading above that is specifying that information belongs to whom,

In one excel sheet only all the data is available.

Super User
Posts: 7,118

Re: how to import excel with headings

EXCEL != SAS.

In Excel you can do all kinds of crazy sh*t. SAS uses a strict, databas-like model, so you CANNOT just "put a header line in there". If you want output to look like that, that's easy, but data is data.

So let's go back to square one: how do you want your SAS dataset(s) to look like (column names, types, formats), and how is the excel sheet structured (what information is found in which cell(s)).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 111

Re: how to import excel with headings

Thanks

Super User
Super User
Posts: 7,565

Re: how to import excel with headings

Hi,

Sorry, I am afraid your question is not clear.  Does your first set of data all appear on one sheet?  If so then you have problems.  Excel really isn't a tool you want to be using in conjunction with any process.  SAS (and any other decent data processing/storage software) works on the basis of tables with column headers and rows of data.  Excel works on the basis of anything goes, completely random unstrcutured/controlled.  The two are mutually incompatible, as (if my first idea was correct) you can clearly see from the fact that you have two datasets in one sheet.

Now you could attempt to read in various cell references, but then you need to keep updating your program.  Or you can split your data out into relevant sections (i.e. tab 1 is data for employee data, tab 2 is for student data), save as csv and then write an import program into SAS.

Am afraid there is no easy "sort out my unstructured, anything goes, Excel document into a useable format" button.

In your second data item, how is that to look in terms of SAS datasets?  I am assuming you would have one dataset called EMP_DETAIL with a label maybe of "Employee Details on 10/03/2015", however then it becomes quite difficult to work with the date - which is data.  Therefore I would suggest your dataset would have that date in rather than as a label e.g.

Dataset EMP_DETAIL is:

DATE               ENO      ENAME          SALARY

10MAR2015     1               aaa               1000

...

You can then utilise the date if for instance you have more/updated data received later on in the year.

Ask a Question
Discussion stats
  • 7 replies
  • 275 views
  • 2 likes
  • 3 in conversation