- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
How are you reading the file?
If you're using a data step you have full control over the variable names.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.