BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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;

5 REPLIES 5
knveraraju91
Barite | Level 11

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

Ksharp
Super User

Also try range:

 

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

 

Aldert
SAS Employee

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

 

range='Sheet1$A2:'

 

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4898 views
  • 2 likes
  • 5 in conversation