Provided Headers vs. Provided Dataset without Headers

[Tom]

Do you still have the raw files with the headers and the data? Are the text files? Are the fields delimited? 

Let's assume that you have text files that are delimited.  For example CSV files that look like these two:

data.csv

Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
Carol,F,14,62.8,102.5
Henry,M,14,63.5,102.5

headers.csv

Name,Sex,Age,Height,Weight

So you could use PROC IMPORT to read the data file

proc import file='data.csv' dbms=dlm 
  out=mydata replace;
  delimiter=',';
  getnames=no;
run;

and it will generate a dataset with variables named VAR1 to VAR5 .

You could do the same to the headers file and it will also generate a dataset with variables names VAR1-VAR5 ;

proc import file='headers.csv' dbms=dlm 
  out=myheaders replace;
  delimiter=',';
  getnames=no;
run;

You could then convert that dataset using PROC TRANSPOSE to one row per variable and two columns, _NAME_ and COL1.

proc transpose data=myheaders out=mynames ;
  var _all_;
run;

So you get a dataset whose contents looks like this:

_NAME_ COL1
 VAR1  Name
 VAR2  Sex
 VAR3  Age
 VAR4  Height
 VAR5  Weight

Now you have the information you need to generate the OLD=NEW pairs needed for a RENAME statement to fix the variable names in the dataset that has the actual data.  

If you only have 900 variables you could even generate the list of pairs into a macro variable.

proc sql noprint ;
  select catx('=',_name_,col1)
    into :renames separated by ' ' 
    from mynames
  ;
quit;

You can then use that macro variable to generate the required RENAME statement or dataset option.

For example you could copy the data into a new database and change the variable names using a data step like this.

data final ;
   set mydata ;
   rename &renames ;
run;

 Or you could look into how to use PROC DATASETS to rename the variables without copying the data.

[GalacticAbacus]

Hi Tom, 

I will try to make time to do the following excersies just to become more familiar with basic sql processes. I used more sql in the past than I do now and I've gotten a little rusty, and I did not know much to begin with. I do wish to learn it through and through however so thanks for outlining in detail.

I have corrected my code, ran the macro and called the macro and it seems to run correctly, however the columns are mis-matched by one, with the first column name being "RENAME" which I assume is caused by if _n_=1 then put 'RENAME'?

Just so I understand, I believe my confusion came from not understanding we were constructing an ad-hoc function via kind-of custom cancatenating some text. If the function "RENAME" is (RENAME  "old" = "new";), so in my case, I'm brining in "RENAME" and "=", and constructing "old" and "new". 

RENAME  cats('F',_n_)  =  Name_Convention;

[Tom]

The program should be generating a rename statement.  If you have three variables named F1,F2,F3 that you want to rename to ID, NAME,AGE then the program should generate a file that looks like this:

RENAME
F1 =ID
F2 =NAME
F3 =AGE
;

If the names are off by one then the logic that you are using to generate F1, F2, etc is off by one.  If you cannot figure out why that happened but it is consistently happening then just adjust the statement that is generating the old names.  So if you changed it to

oldname = cats('F',_N_ + 1) ;

then it would generate.

RENAME
F2 =ID
F3 =NAME
F4 =AGE
;

[GalacticAbacus]

Hi Tom, 

I actually corrected it last week and it worked fine. Thx again for the help, I'll approve the starting solution and work on applying the general concepts here. Great learning for me so I appreciate the time spent. 

TS