topic rename the column attributes from the first row values in SAS Procedures

rename the column attributes from the first row values

1239 — Wed, 04 Apr 2012 10:37:59 GMT

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)

ABC	F1	F2	FDG
ID	Name	Sex	Country
1	ABC	M	IND
2	BCD	F	USA
3	CDE	M	GER
4	DGE	M	UK

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

ID	Name	Sex	Country
1	ABC	M	IND
2	BCD	F	USA
3	CDE	M	GER
4	DGE	M	UK

Thanks in Advance.

rename the column attributes from the first row values

1239 — Wed, 04 Apr 2012 11:12:09 GMT

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.

rename the column attributes from the first row values

1239 — Wed, 04 Apr 2012 11:21:33 GMT

I got the below error:

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.

Re: rename the column attributes from the first row values

Hima — Wed, 04 Apr 2012 11:40:41 GMT

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

rename the column attributes from the first row values

1239 — Wed, 04 Apr 2012 12:09:41 GMT

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)

ABC	F1	F2	FDG
ID	Name	Sex	Country
1	ABC	M	IND
2	BCD	F	USA
3	CDE	M	GER
4	DGE	M	UK

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

ID	Name	Sex	Country
1	ABC	M	IND
2	BCD	F	USA
3	CDE	M	GER
4	DGE	M	UK

rename the column attributes from the first row values

Hima — Wed, 04 Apr 2012 12:22:18 GMT

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;

rename the column attributes from the first row values

1239 — Wed, 04 Apr 2012 12:32:05 GMT

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

rename the column attributes from the first row values

Hima — Wed, 04 Apr 2012 12:39:28 GMT

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.

rename the column attributes from the first row values

Hima — Wed, 04 Apr 2012 12:51:49 GMT

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

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

Re: rename the column attributes from the first row values

Linlin — Wed, 04 Apr 2012 13:18:44 GMT

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

Re: rename the column attributes from the first row values

Ksharp — Fri, 06 Apr 2012 08:54:08 GMT

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

Re: rename the column attributes from the first row values

1239 — Fri, 06 Apr 2012 09:09:06 GMT

Thanks a lot, the code is working as expected.