DATA Step, Macro, Functions and more

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

Reply
Super Contributor
Posts: 272

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

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;

Trusted Advisor
Posts: 1,566

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

Posted in reply to knveraraju91

Have you tried with datarow=3 ?

Super Contributor
Posts: 272

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

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

Super User
Posts: 10,028

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

[ Edited ]
Posted in reply to knveraraju91

Also try range:

 

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

 

Super User
Posts: 11,343

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

Posted in reply to knveraraju91

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

Ask a Question
Discussion stats
  • 4 replies
  • 412 views
  • 1 like
  • 4 in conversation