Hello everyone,
I have a sas dataset that has values like below.
The variable name "LSIDEM01_Listing_of_Subject_Dem" is very big. I want the observation 1 values to be variable names for all the B, C, D, E, F, G, H....
Transpose the first row.
proc transpose data=HAVE(obs=1) out=names name=oldname;
var _all_;
run;
Now convert the gibberish into something that could be used as a variable name, you can use the original gibberish as the label.
data names;
length varnum 8 oldname name $32 label $256 ;
set names;
varnum+1;
label=col1;
* Replace adjacent non-valid characters with single underscore ;
name=translate(trim(prxchange('s/([^a-zA-Z0-9]+)/ /',-1,col1)),' _','_ ');
name=prxchange('s/(^[0-9])/_$1/',1,name);
drop col1;
run;
Now generate code to rename the variables and attach the labels.
filename code temp;
data _null_;
file code;
put 'rename' ;
do p=1 to nobs;
set names point=p nobs=nobs;
if upcase(name) ne upcase(oldname) then
put oldname '=' name
;
end;
put ';' ;
do p=1 to nobs;
set names point=p;
if label ne name then do;
label=quote(trim(label),"'");
put 'label ' oldname '=' label ';' ;
end;
end;
stop;
run;
Now you can use the generated RENAME and LABEL statements in either a new data step.
data want;
set have(firstobs=2);
%include code / source2;
run;
Or as part of a PROC DATASETS step.
proc dataset nolist lib=work;
modify have ;
%include code / source2;
run;
quit;
How was the dataset you have created? With names like B,C,D,... it looks like you imported a spreadsheet. So why not take a step back and create the dataset with valid names to begin with?
@RAVI2000 wrote:
Thanks @Tom It was imported from excel, I didn't create it. It comes from a different team and I cannot edit it. I can only import and then do more modifications.
Your first and last sentences contradict each other. Assuming the last one is correct and it was you that created the dataset by importing an Excel file then just change how you imported it.
proc import file='somefile.xlsx' dbms=xlsx out=want replace;
range='$A2:';
run;
@RAVI2000 wrote:
Thanks @Tom It was imported from excel, I didn't create it. It comes from a different team and I cannot edit it. I can only import and then do more modifications.
You could ... politely ... ask them to re-do it such that the problem is fixed. To me this is the optimal solution. You're lucky @Tom has figured out how to fix this in SAS, I would not have even tried.
Transpose the first row.
proc transpose data=HAVE(obs=1) out=names name=oldname;
var _all_;
run;
Now convert the gibberish into something that could be used as a variable name, you can use the original gibberish as the label.
data names;
length varnum 8 oldname name $32 label $256 ;
set names;
varnum+1;
label=col1;
* Replace adjacent non-valid characters with single underscore ;
name=translate(trim(prxchange('s/([^a-zA-Z0-9]+)/ /',-1,col1)),' _','_ ');
name=prxchange('s/(^[0-9])/_$1/',1,name);
drop col1;
run;
Now generate code to rename the variables and attach the labels.
filename code temp;
data _null_;
file code;
put 'rename' ;
do p=1 to nobs;
set names point=p nobs=nobs;
if upcase(name) ne upcase(oldname) then
put oldname '=' name
;
end;
put ';' ;
do p=1 to nobs;
set names point=p;
if label ne name then do;
label=quote(trim(label),"'");
put 'label ' oldname '=' label ';' ;
end;
end;
stop;
run;
Now you can use the generated RENAME and LABEL statements in either a new data step.
data want;
set have(firstobs=2);
%include code / source2;
run;
Or as part of a PROC DATASETS step.
proc dataset nolist lib=work;
modify have ;
%include code / source2;
run;
quit;
I have tried your code, and I work on cloud studio.
The "filename" statement is not reading the correct temp path.
The code has been updated to account for the missing NOBS= option and other issues.
But if you still ahve the original XLSX file you don't need it. You can tell SAS to skip the first line when importing the file. That will also allow SAS to discover any numeric variables that might be in the there since it no longer will treat the column headers as data.
Example:
proc import datafile="mydatafile.xlsx" out=abc dbms=excel replace;
getnames=yes;
run;
Use the RANGE option to specify the upper left corner and PROC IMPORT will import that.
So to skip the first row then start in A2. To skip 4 lines start in A5.
proc import file='c:\downloads\test_range.xlsx' dbms=xlsx out=want replace;
range='$A2:' ;
run;
If the file also includes junk below or to the right of the table then specify the full range.
proc import file='c:\downloads\test_range.xlsx' dbms=xlsx out=want replace;
range='$A2:E18' ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.