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!
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
How are you reading the file?
If you're using a data step you have full control over the 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,
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.
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.
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!
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
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!
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.