BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
1239
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

11 REPLIES 11
1239
Calcite | Level 5

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.

1239
Calcite | Level 5

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.

Hima
Obsidian | Level 7

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$A2:D6";

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$A2:D6";
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

1239
Calcite | Level 5

I got below error.

ERROR: File _IMEX_.'Invoice$A2:D6'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
Hima
Obsidian | Level 7

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 "A2:D6". 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;

1239
Calcite | Level 5

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

Hima
Obsidian | Level 7

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$A2:D6"; ** 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.

Hima
Obsidian | Level 7

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

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

Linlin
Lapis Lazuli | Level 10

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

1239
Calcite | Level 5

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

Ksharp
Super User

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 11 replies
  • 24418 views
  • 1 like
  • 4 in conversation