topic Re: how to import excel with headings in SAS Procedures

how to import excel with headings

Ravikumarkummari — Tue, 10 Mar 2015 11:35:41 GMT

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
1	aaa	1000
2	bbb	2000
3	ccc	3000
4	ddd	4000
5	eee	5000
6	aaa	6000
7	bbb	7000
8	ccc	8000
9	ddd	9000
10	eee	10000
Student Details on 10/03/2015
std_id	std_name	marks
101	aaa	50
102	bbb	60
103	ccc	95
104	ddd	82
105	eee	96
106	aaa	74
107	bbb	52
108	ccc	38
109	ddd	40
110	eee	64

the output is like this.

Employee Details on 10/03/2015
eno	ename	salary
1	aaa	1000
2	bbb	2000
3	ccc	3000
4	ddd	4000
5	eee	5000
6	aaa	6000
7	bbb	7000
8	ccc	8000
9	ddd	9000
10	eee	10000
Student Details on 10/03/2015
101	aaa	50
102	bbb	60
103	ccc	95
104	ddd	82
105	eee	96
106	aaa	74
107	bbb	52
108	ccc	38
109	ddd	40
110	eee	64

Re: how to import excel with headings

Kurt_Bremser — Tue, 10 Mar 2015 11:45:05 GMT

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

Re: how to import excel with headings

RW9 — Tue, 10 Mar 2015 11:48:08 GMT

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.

Re: how to import excel with headings

Ravikumarkummari — Tue, 10 Mar 2015 11:51:35 GMT

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.

Re: how to import excel with headings

Kurt_Bremser — Tue, 10 Mar 2015 12:09:45 GMT

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)

Re: how to import excel with headings

Ravikumarkummari — Tue, 10 Mar 2015 12:16:56 GMT

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.

Re: how to import excel with headings

Kurt_Bremser — Tue, 10 Mar 2015 12:40:08 GMT

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)).

Re: how to import excel with headings

Ravikumarkummari — Tue, 10 Mar 2015 13:05:04 GMT

Thanks