Covert the first observation values to variabale names

[RAVI2000]

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....

[Tom]

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]

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.

[Tom]

---

@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;

[PaigeMiller]

---

@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.

[RAVI2000]

Yes, going forward we have raised a request. That is really not a good data to work on with.

[Tom · Accepted solution]

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;

[RAVI2000]

I have tried your code, and I work on cloud studio.

The "filename" statement is not reading the correct temp path.

[Tom]

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.

[RAVI2000]

What option in proc import do I specify for it? I have tried it with range and datarow options. I didn't get exact row of the data that I am looking for. This excel has the variables values in the first observation. But other sheet have the values in 4th , 5th observations.

[PaigeMiller]

Example:

proc import datafile="mydatafile.xlsx" out=abc dbms=excel replace;
    getnames=yes;
run;

[RAVI2000]

Thank you very much @Tom It really helped me alot!

[Tom]

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;