Use row 1 as variable names

[pamplemousse822]

Hello, 

I am trying to figure out how to use values in row 1 as variable names. Some of these values need to be editted before they can be used as var names (e.g., they begin with %, have a space, or period). 

For example, the data I have looks like this: 

A B C D 

Sample Well %Mean Calc. Concentration

...

And I want these as variable names: 

Sample Well PctMean CalcConcentration 

...

I've seen a proc transpose/proc sql method, but I can't get it to work because the values are not in a correct format for variable names. 

Ideas? 

Thank you!

[Reeza]

How are you reading the file?

If you're using a data step you have full control over the variable names. 

[pamplemousse822]

Now it's in a data step. How can I tell SAS to pull the second row as variable names? I only know how to manually do this.. 

Thanks,

[Reeza]

Try using the FIRSTOBS= option to control the first row of data. 

If you're reading it via a data step you have to manually type out the variable names/types, more control means more work. 

[Kurt_Bremser]

What is the reason for this? This first line does not convey any information about column attributes, so you will have to rewrite your data step in case of a change anyway.

[pamplemousse822]

I figured out another way! I have an excel file which needs to be split into sections, each with different variable names. I just learned of the range option in proc import which has solved my problem. 

Initially, i had imported the entire table, and so wanted a solution to grab the variable names the middle of the shet. 

Thanks! 

[TomKari · Accepted solution]

Would this work?

1. Data step 1

Read the first row

Transform the values to valid variable names (strip out blanks, invalid characters, shorten)

Load them into a macro variable

2. Data step 2

Start reading at row 2, use the macro variable from step 1 after an "input" statement.

Tom

[pamplemousse822]

I think this would work. I tried doing it this way, and then learned about the range option in proc import which has solved my problem. Thank you! 

[Tom]

If you really needed to do this you could ask PROC IMPORT to do it for you.

Write the file to a CSV file without headers and then use PROC IMPORT to read it back in.

filename csv temp;
data _null_;
  set have ;
  file csv dsd ;
  put (_all_) (+0);
run;
proc import datafile=csv dbms=dlm 
  out=want replace
;
  delimiter=',';
  getnames=yes;
run;

If you didn't want to let PROC IMPORT change the types of all of the data then you could only write the first line to the text file and then merge the proc contents data for your old and new tables by VARNUM and generate RENAME (and also LABEL) statement.