topic Re: How to import data in Excel file with heading and variables names in first and second rows in SAS Programming

How to import data in Excel file with heading and variables names in first and second rows

knveraraju91 — Tue, 25 Oct 2016 16:20:09 GMT

Dear,

In the Excel file , the first row contains header and second row contains variable names. From third row data starts. I need to read variable names and data.

My code getting the header in the first variable name column and variable names appear in first row of data.

Please help. Thanks

excel data;

A B C D E

HEADER NAME HEADERNAME

FILE TERM LLT HLT PT

EVENT ANEMIA ANEMIA ANEMIA ANEMIA

OUTPUT GETTING;

HEADER NAME HEADERNAME B C D E

FILE TERM LLT HLT PT

EVENT ANEMIA ANEMIA ANEMIA ANEMIA

OUTPUT NEEDED;

FILE TERM LLT HLT PT

EVENT ANEMIA ANEMIA ANEMIA ANEMIA

my code:

OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

proc import datafile="C:\Users\aa\Desktop\one.xlsx" DBMS=xlsx out=co;datarow=2;

run;

Re: How to import data in Excel file with heading and variables names in first and second rows

Shmuel — Tue, 25 Oct 2016 19:37:00 GMT

Have you tried with datarow=3 ?

Re: How to import data in Excel file with heading and variables names in first and second rows

knveraraju91 — Wed, 26 Oct 2016 00:40:22 GMT

Yes I tried. But I am not getting the variable names. How to get the variable names. Thanks.

Re: How to import data in Excel file with heading and variables names in first and second rows

Ksharp — Wed, 26 Oct 2016 03:03:39 GMT

Also try range:

proc import ..........
range='Sheet1$A2:D100'
 
range='A2:D100'

Re: How to import data in Excel file with heading and variables names in first and second rows

ballardw — Wed, 26 Oct 2016 14:39:19 GMT

I am going to assume that your "header" row contains more detailed information about the variable. Such as header "Value at purchase" or some thing longer and that the variable name, which might be VP is the preferred name of the variable for datasets and such.

If that is the case here is what I would do (and have done MANY times).

1) Cut the header row and paste into another spreadsheet to save it. It actually makes sense to post transposed.

2) Import the data set without the header row. The VARIABLE names will be from row 1(since the header has been removed)

3) You can use proc data sets and the information in the saved header to row to create LABELS that SAS will display for most purposes in stead of the variable Name.

The Proc Datasets code would look like:

Proc Datasets library=mylib; /* mylib being the library the data set was saved to. possibly WORK from your example*/

Modify co; /* the name of the data set*/

label

var1 = "heading 1"

var2 = "heading 2"

/* continue till all of the variables are accounted for

;

run;

quit;

Note that by also copy and paste transpose of both the header row and variable you would have columns and could use Excel functions to create text like Var="heading 1" that you could copy and paste out of Excel into the code for the label statement.

This particular operation actuall accounts for about 10 to 15% of my actual data manipulation in Excel

Re: How to import data in Excel file with heading and variables names in first and second rows

Aldert — Wed, 06 May 2020 07:11:27 GMT

As you do not always know the width and length of your data, you can also use:

range='Sheet1$A2:'