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;
Have you tried with datarow=3 ?
Yes I tried. But I am not getting the variable names. How to get the variable names. Thanks.
Also try range:
proc import ..........
range='Sheet1$A2:D100'
range='A2:D100'
As you do not always know the width and length of your data, you can also use:
range='Sheet1$A2:'
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.