SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Use row 1 as variable names

Reply
New Contributor
Posts: 4

Use row 1 as variable names

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!

 

 

Super User
Posts: 19,876

Re: Use row 1 as variable names

How are you reading the file?

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

New Contributor
Posts: 4

Re: Use row 1 as variable names

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,

Super User
Posts: 19,876

Re: Use row 1 as variable names

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. 

Super User
Posts: 7,866

Re: Use row 1 as variable names

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: Use row 1 as variable names

Posted in reply to KurtBremser

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! 

PROC Star
Posts: 1,167

Re: Use row 1 as variable names

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

New Contributor
Posts: 4

Re: Use row 1 as variable names

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! 

Super User
Super User
Posts: 7,079

Re: Use row 1 as variable names

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.

Ask a Question
Discussion stats
  • 8 replies
  • 254 views
  • 0 likes
  • 5 in conversation