Help using Base SAS procedures

rename the column attributes from the first row values

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

rename the column attributes from the first row values

Hi,

I have .xlsb raw data and once it is imported in SAS, the data structure will be as shown below. My requirement is to rename the column names automatically from the First row values as showin in Desired data. Please help me in achieving this.

PROC IMPORT OUT= test

            DATAFILE= "C:\Book1.xlsb"

            DBMS=EXCEL REPLACE;

     RANGE="sheet1$";

           GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

--test dataset data structure (After Importing structure will be as shown below)

ABCF1F2FDG
IDNameSexCountry
1ABCMIND
2BCDFUSA
3CDEMGER
4DGEMUK

--Desired Data (from the test dataset how to achieve as shown below)

IDNameSexCountry
1ABCMIND
2BCDFUSA
3CDEMGER
4DGEMUK

Thanks in Advance.


Accepted Solutions
Solution
‎04-04-2012 09:18 AM
Super Contributor
Posts: 1,636

Re: rename the column attributes from the first row values

Hi 1239,

try this one (there are other solutions at https://communities.sas.com/message/122471#122471):

data have;

input (ABC F1 F2 FDG)($);

cards;

ID Name Sex Country

1 ABC M IND

2 BCD F USA

3 CDE M GER

4 DGE M UK

;

proc transpose data=have(obs=1) out=temp;

var _all_;

run;

proc sql ;

   select catx('=',_name_,col1)

     into :rename separated by ' '

        from temp;

quit;

data have;

  set have(firstobs=2 rename=(&rename));

run;

proc print;run;

                               Obs    ID    Name    Sex    Country

                                1     1     ABC      M       IND

                                2     2     BCD      F       USA

                                3     3     CDE      M       GER

                                4     4     DGE      M       UK

Linlin

View solution in original post


All Replies
Contributor
Posts: 55

rename the column attributes from the first row values

Thanks for the code but I got the below error.

Spreadsheet isn't from Excel V5 or later.  Please open it in Excel and Save as V5 or later

Requested Input File Is Invalid

ERROR: Import unsuccessful.  See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

Please help me.

Contributor
Posts: 55

rename the column attributes from the first row values

I got the below error:

NOTE: The previous statement has been deleted.

NOTE: The previous statement has been deleted.

NOTE: The previous statement has been deleted.

179                                                 namerow=2;

                                                    -------

                                                    180

180                                                 startrow=3;

                                                    --------

                                                    180

ERROR 180-322: Statement is not valid or it is used out of proper order.

181                                                 getnames=yes;

182                                                 run;

NOTE: The SAS System stopped processing this step because of errors.

Regular Contributor
Posts: 233

Re: rename the column attributes from the first row values

Tested and working fine.

PROC IMPORT OUT=WORK.OP

            FILE="C:\Documents and Settings\test.xlsb"

            DBMS=EXCEL REPLACE;

   GETNAMES=YES;

   SCANTEXT=YES;

   USEDATE=NO;

   SCANTIME=YES;

   DBSASLABEL=NONE;

   TEXTSIZE=512;

   RANGE="Invoice$A2Smiley Very Happy6";

RUN;

proc print; run;

Output:

Obs    ID    Name    Sex    Country

                               1      1    ABC      M       IND
                               2      2    BCD      F       USA
                               3      3    CDE      M       GER
                               4      4    DGE      M       UK


Log:

112  PROC IMPORT OUT=WORK.OP
113              FILE="C:\Documents and Settings\test.xlsb"
114              DBMS=EXCEL REPLACE;
115     GETNAMES=YES;
116     SCANTEXT=YES;
117     USEDATE=NO;
118     SCANTIME=YES;
119     DBSASLABEL=NONE;
120     TEXTSIZE=512;
121     RANGE="Invoice$A2Smiley Very Happy6";
122
123  RUN;

NOTE: WORK.OP data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           34.92 seconds
      cpu time            1.12 seconds


125  proc print; run;

NOTE: There were 4 observations read from the data set WORK.OP.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Contributor
Posts: 55

rename the column attributes from the first row values

I got below error.

ERROR: File _IMEX_.'Invoice$A2Smiley Very Happy6'n.DATA does not exist.

ERROR: Import unsuccessful.  See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           1.35 seconds

      cpu time            0.04 seconds

I think small confusion has created. My .xlsb data structure will be different.

If you look at my orginal post after importing .xlsb, I will create one dataset called "test". Test dataset structure will be as shown below. From test dataset variable names to be renamed.

PROC IMPORT OUT= test

            DATAFILE= "C:\Book1.xlsb"

            DBMS=EXCEL REPLACE;

     RANGE="sheet1$";

           GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

--test dataset data structure (After Importing structure will be as shown below)

ABCF1F2FDG
IDNameSexCountry
1ABCMIND
2BCDFUSA
3CDEMGER
4DGEMUK

--Desired Data (from the test dataset how to achieve as shown below)

IDNameSexCountry
1ABCMIND
2BCDFUSA
3CDEMGER
4DGEMUK
Regular Contributor
Posts: 233

rename the column attributes from the first row values

This can be done in 2 ways. One is the way that I proposed. Dropping the first row when importing. The second one is the process you proposed.

Talking about the first process, the error occured because the file you are trying to import for example "A2Smiley Very Happy6". That resolves the error.

If you want to do it the other way, see the below code.

data have;
input ABC$ F1$ F2 $ FDG $;
cards;
ABC F1 F2 FDG
ID Name Sex Country
1 ABC M IND
2 BCD F USA
3 CDE M GER
4 DGE M UK
run;

data want (RENAME=(ABC = ID
       F1=Name
       F2=sex
       FDG=Country));
set have;
if _n_=1 then delete;
run;

proc print; run;

Contributor
Posts: 55

rename the column attributes from the first row values

Sometimes column names will be more than what I specified in real time. Is there any way to capture the first rows values and renaming the column names automatically instead of explicity specifying in the code?

ABC F1 F2 FDG F3 GH KL OI.....

Thanks

Regular Contributor
Posts: 233

rename the column attributes from the first row values

Then I would go with the first process where we have the ability to drop first row while importing. Change the bold values according to your need.

PROC IMPORT OUT=WORK.OP  ** Change the library name and DSN**;

            FILE="C:\Documents and Settings\test.xlsb" ** Change the Path**;

            DBMS=EXCEL REPLACE;

   GETNAMES=YES;

   SCANTEXT=YES;

   USEDATE=NO;

   SCANTIME=YES;

   DBSASLABEL=NONE;

   TEXTSIZE=512;

   RANGE="Invoice$A2Smiley Very Happy6"; ** Replace the word Invoice with your Sheet name**;  ** Enter the range of data that you want. Make sure you exclude the first row **;

RUN;

proc print; run;

Try it and let me know how it works.

I will check if there are other options available related to this scenario.

Regular Contributor
Posts: 233

rename the column attributes from the first row values

There is one more way. Please check the below link.

http://www.lexjansen.com/pharmasug/2008/cc/cc04.pdf

Solution
‎04-04-2012 09:18 AM
Super Contributor
Posts: 1,636

Re: rename the column attributes from the first row values

Hi 1239,

try this one (there are other solutions at https://communities.sas.com/message/122471#122471):

data have;

input (ABC F1 F2 FDG)($);

cards;

ID Name Sex Country

1 ABC M IND

2 BCD F USA

3 CDE M GER

4 DGE M UK

;

proc transpose data=have(obs=1) out=temp;

var _all_;

run;

proc sql ;

   select catx('=',_name_,col1)

     into :rename separated by ' '

        from temp;

quit;

data have;

  set have(firstobs=2 rename=(&rename));

run;

proc print;run;

                               Obs    ID    Name    Sex    Country

                                1     1     ABC      M       IND

                                2     2     BCD      F       USA

                                3     3     CDE      M       GER

                                4     4     DGE      M       UK

Linlin

Contributor
Posts: 55

Re: rename the column attributes from the first row values

Thanks a lot, the code is working as expected.Smiley Happy

Super User
Posts: 9,682

Re: rename the column attributes from the first row values

Maybe you can try to define a range like :

PROC IMPORT OUT= test

            DATAFILE= "C:\Book1.xlsb"

            DBMS=EXCEL REPLACE;

     RANGE='sheet1$A1:X100'n;

           GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

Ksharp

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 8865 views
  • 0 likes
  • 4 in conversation