topic Re: Provided Headers vs. Provided Dataset without Headers in SAS Data Management

Provided Headers vs. Provided Dataset without Headers

GalacticAbacus — Wed, 11 Nov 2015 17:26:34 GMT

Hi,

Background:

I have a data set without headers (we'll call it "Why"). This dataset has 900+ variable columns. I unziped, and convereted the file to a .txt and successfully imported this data set into SAS EG.

I then have a .txt file with 900+ headers (we'll call it "Useful"), containing values to be associated with the large "Why" data set variables.

Both files (Why, with 900 variable columns and about and 1.3 million rows), and (Useful, with 900 columns and 1 row), have been imported into SAS and are sitting as SAS files within my Work.directory.

Question:

What is the best way to attach the "Useful" file headers to the "Why" file without headers to the appropriate column?

Why are headers not attached to datasets?

Re: Provided Headers vs. Provided Dataset without Headers

kannand — Wed, 11 Nov 2015 19:08:31 GMT

When you refer to headers, do you mean the header row that refers to the column names? Sorry if it's a basic question.....just trying to understand.

Re: Provided Headers vs. Provided Dataset without Headers

GalacticAbacus — Wed, 11 Nov 2015 20:39:01 GMT

No prob, yes the header row that refers to the column names

Re: Provided Headers vs. Provided Dataset without Headers

FreelanceReinh — Fri, 13 Nov 2015 18:01:24 GMT

Good question from Kannan. I was also wondering. Perhaps you mean variable label, i.e., a short description of a variable's content (e.g. "Patient number" for a variable named PATNO)?

After all, even if the "headers" found in the raw data are some kind of "names", would we be sure that these names happen to be valid SAS names? So, it would be safer to regard them just as "labels".

In this case, there were several answers posted to a similar question in the SAS-L mailing list, 15 years ago:

https://groups.google.com/forum/#!topic/comp.soft-sys.sas/awuwQAwP1AA

However, my impression was that your situation is slightly different.

There is also a 2008 paper "Dynamically Create Variable Labels from Data Set Values" which may be applicable, but I haven't checked that in detail either.

Instead, I've quickly written two macros, which seem to work at least for the test data I've created (26 columns, 13 rows). The first changes the labels of the variables in dataset WHY to the "headers" found in the 1-observation dataset USEFUL, the second changes the variable names correspondingly (assuming that the headers are in fact valid SAS variable names).

/* Macro to label variables in &DATA with labels found in &LABELS */

%macro lblvars(data=, labels=);

  /* Retrieve variable names */
  proc sql noprint;
  select name into :vn1 - :vn9999
    from dictionary.columns
    where libname='WORK' & memname=upcase("&data")
    order by varnum;
  quit;

  /* Retrieve variable labels */
  data _null_;
  set &labels;
  array lv[*] _character_;
  do i=1 to &sqlobs;
    call symput(cats('lbl',i), lv[i]);
  end;
  run;

  %put Now labeling &sqlobs variables ...;
   
  proc datasets nolist;
  modify &data;
  label 
  %do i=1 %to &sqlobs;
    &&vn&i="&&lbl&i"
  %end;;
  quit;

  %put ... Done.;
%mend lblvars;


/* Macro to rename variables in &DATA with new names found in &NAMES */

%macro renvars(data=, names=);

  /* Retrieve old variable names */
  proc sql noprint;
  select name into :vn1 - :vn9999
    from dictionary.columns
    where libname='WORK' & memname=upcase("&data")
    order by varnum;
  quit;

  /* Retrieve new variable names */
  data _null_;
  set &names;
  array nv[*] _character_;
  do i=1 to &sqlobs;
    call symput(cats('name',i), nv[i]);
  end;
  run;

  %put Now renaming &sqlobs variables ...;
   
  proc datasets nolist;
  modify &data;
  rename 
  %do i=1 %to &sqlobs;
    &&vn&i=&&name&i
  %end;;
  quit;

  %put ... Done.;
%mend renvars;


/* Create test data */

%let nvar=26;

data why;
array v[&nvar];
do i=1 to 13;
  do _n_=1 to &nvar;
    v[_n_]=ranuni(31416);
  end;
  output;
end;
drop i;
run;

data useful;
array hdr[&nvar] $40;
do _n_=1 to &nvar;
  hdr[_n_]=byte(64+_n_);
end;
run;

proc contents data=why;
run;


/* Apply the macros and see how meta data change */

%lblvars(data=why, labels=useful)

proc contents data=why;
run;

%renvars(data=why, names=useful)

proc contents data=why;
run;

Of course, this is just unvalidated draft code and it comes with no warranties whatsoever. In particular, the macros might fail if the headers are invalid labels or names, respectively.

Obviously, it is crucial that the n-th column of USEFUL contains the header for the n-th column of WHY for all n=1, ..., 900+.

As to your second question, I agree that it should be easier to retrieve variable names and labels from raw data.

Edit: For delimited raw data files such as CSV files there is in fact a way to retrieve variable names from the first record of the file: see PROC IMPORT, GETNAMES statement.

Re: Provided Headers vs. Provided Dataset without Headers

Tom — Wed, 11 Nov 2015 20:57:20 GMT

The answer is simple. Use a RENAME statement. Either in the program that reads the data or later using PROC DATASETS.

You can generate the rename statement from the list of variable names.

So let's assume that you have created a SAS dataset with variables named VAR1 to VAR900.

So you could read in the list of names and generate the rename statement with a simple data step.

filename rename temp;
data _null_;
   infile 'headers' end=eof;
   length oldname newname $32 ;
   input newname;
   file rename ;
   if _n_=1 then put 'RENAME';
   oldname=cats('VAR',_n_);
   put oldname '=' newname ;
   if eof then put ';' ;
run;

Now you can use that rename statement to change the names.

data fixed;
   set original;
%include rename ;
run;

Re: Provided Headers vs. Provided Dataset without Headers

kannand — Wed, 11 Nov 2015 21:19:31 GMT

Here is another way that might be simpler depending on what you have.......with a PROC SQL. The IN dataset is what has the headers. The "test" dataset does not have proper headers but has names like v1, v2, v3, v4, v5. This data is inserted into IN as shown below....

DATA IN;
	 SET SASHELP.CLASS(OBS=1);
RUN;

data test;
	infile datalines dlm='|';
	input  V1:$8. V2:$1. V3:4. V4:4. V5:4.;
datalines;
Alice|F|13|56.5|84.0
Barbara|F|13|65.3|98.0
;
RUN;
PROC SQL;
	INSERT INTO IN SELECT * FROM TEST;
QUIT;

PROC PRINT DATA=IN;TITLE '==IN';
PROC PRINT DATA=TEST;TITLE '==TEST';

Please note that this involves I/O read/writes but is a simple INSERT statement interms of coding.

The output results shows this below:

==IN
Obs	Name	Sex	Age	Height	Weight
1	Alfred	M	14	69.0	112.5
2	Alice	F	13	56.5	84.0
3	Barbara	F	13	65.3	98.0
==TEST
Obs	V1	V2	V3	V4	V5
1	Alice	F	13	56.5	84
2	Barbara	F	13	65.3	98

Hope this helps...!!!

Re: Provided Headers vs. Provided Dataset without Headers

GalacticAbacus — Thu, 12 Nov 2015 18:19:12 GMT

Hi, I believe your solution is what I need but is coded in a way I don't quite follow.

I've outlined what I think you are doing... would you mind elaborating?

filename rename temp;              (I assume this is renaming the temp table I'm creating)
data _null_;                       (don't create table when executing the following functions)
   infile 'headers' end=eof;       (read the file 'headers into SAS)
   length oldname newname $32 ;    (create two new variables 'oldname' & 'newname' length $32)
   input newname;                  (specify perviously created variable 'newname'?)
   file rename ;                   (Here is the rename statement but is what follows what is being defined as my old value and new value?)
   if _n_=1 then put 'RENAME';     (If the first time the data step has iterated = 1 then....what is put 'RENAME' doing?)
   oldname=cats('VAR',_n_);
   put oldname '=' newname ;
   if eof then put ';' ;
run;

Re: Provided Headers vs. Provided Dataset without Headers

Tom — Thu, 12 Nov 2015 19:01:48 GMT

This step just creates a file with the rename statement. The syntax for a rename statement is: RENAME old=new old=new .... ;

* Create a temporary file to store the generated code ;
* Use the filref of RENAME to refer to it.;
filename rename temp; 

* Run a datastep without any output datasets ;
data _null_;  

* Read from the text file that lists the headers. Set EOF flag ;
   infile 'headers' end=eof;   

* Define two variables to use to store the names. SAS names are limited to 32 bytes. ;
   length oldname newname $32 ;  

* Read the next name from the input file. ;
   input newname; 

* Generate the next dummy name by concatenating the loop counter to the string 'VAR' ;
   oldname=cats('VAR',_n_);

* Specity that PUT statement output will go the temporary file instead of the log ;    
   file rename ;

* First time through write the beginning of the rename statement ; 
   if _n_=1 then put 'RENAME';    

* Write the old=new pair for this name ;
   put oldname '=' newname ;

* When at the end of the input then write the semi-colon to finish the  rename statement. ;
   if eof then put ';' ;

* End of the data step definition ;
run;

Re: Provided Headers vs. Provided Dataset without Headers

GalacticAbacus — Thu, 12 Nov 2015 20:29:30 GMT

Hi, thank you very much for talking the time to walk me through the code. If I could ask a couple more questions,

the "infile" statement is what is causing some confusion as I'm getting the error, "not found within the current file path" and it's trying to find my file somewhere other than my "SAS.WORK" directory. The file I am reading from, containing my headers, is a temporary file sitting in my work directory. I checked out other portions of your code by doing some reading and running them seperatly. The concatenation of string "var" with the counter makes sense....

The other part is we seem to be defining "oldname". I assume (input newname;) is where we are reading the variable within my "headers" table as the "newname" to be swapped for "oldname". I think the rest makes sense.

Re: Provided Headers vs. Provided Dataset without Headers

Tom — Thu, 12 Nov 2015 21:18:03 GMT

@GalacticAbacus wrote:

Hi, thank you very much for talking the time to walk me through the code. If I could ask a couple more questions,

the "infile" statement is what is causing some confusion as I'm getting the error, "not found within the current file path" and it's trying to find my file somewhere other than my "SAS.WORK" directory. The file I am reading from, containing my headers, is a temporary file sitting in my work directory. I checked out other portions of your code by doing some reading and running them seperatly. The concatenation of string "var" with the counter makes sense....

The other part is we seem to be defining "oldname". I assume (input newname;) is where we are reading the variable within my "headers" table as the "newname" to be swapped for "oldname". I think the rest makes sense.

It is extremely unlikely that the default directory is the directory that SAS creates automatically for the WORK library. It would normally be the current working directory when the SAS command started running. Or you can change it by running the operarting system's cd command. If the file you want want to read is not in the current working directory then just include the fully quailified name of the file inside the quotes. If the file actual is in your work directory you can use the PATHNAME() function to find out where the WORK library is pointing. So you could change the INFILE to something like this:

INFILE "%sysfunc(pathname(work))/headers.txt" end=eof ;

But that is very unlikely to be what you mean. It is more likely that you mean you have already read the headers into a SAS dataset in the WORK library. If it is a dataset with one observation per header name then just replace the INFILE and INPUT statements with a SET statement. You will also not need the variable NEWNAME as you can just use the variable that already has the names instead. The SET statement also supports the END= option so that you can tell when you get to the end of the list.

* Replace these ;
* INFILE 'header' end=eof' ;
* input newname ;
* With  ;
SET headers END=EOF ;

If the names of the variables that you created when you read the file without the headers are not VAR1 to VAR900 then you will need a similar list of variable names so that you know which old name goes with which new name. If you don't have such a dataset you can generate a dataset with one observation per variable stored in a varaible named _NAME_ by using proc transpose. So if your existing dataset that you want to rename the variables for is named WANT then you can make a dataset named OLDNAMES using this procedure call.

proc transpose data=want(obs=0) out=oldnames ;
  var _all_;
run;

Re: Provided Headers vs. Provided Dataset without Headers

GalacticAbacus — Thu, 12 Nov 2015 21:23:33 GMT

Hi, thank you very much for talking the time to walk me through the code. If I could ask a couple more questions,

Re: Provided Headers vs. Provided Dataset without Headers

FreelanceReinh — Thu, 12 Nov 2015 21:38:47 GMT

Hi @GalacticAbacus,

I'm just curious: Did you give my RENVARS macro a try? It was tailored to the situation you described in your initial post, i.e. two datasets with the same number of variables (900+). Tom's elegant original solution assumed that the "headers" data are a .txt file. Moreover, it required that the variable names of your dataset "WHY" are VAR1, VAR2, etc., whereas macro RENVARS does not make such an assumption.

Re: Provided Headers vs. Provided Dataset without Headers

GalacticAbacus — Thu, 12 Nov 2015 22:08:21 GMT

Hi, I think I am almost there, you were correct in that I ment I had a SAS dataset in my WORK library.

I have two datasets, both I've read into SAS as SAS datasets within my WORK library.

<> Dataset_1 = Work.Headerfile, with one variable "Name Convention" which is a list of my headers.

<> Dataset_2 = Work.Set_1, with 900+ variables and 1.2 million rows. Each variable data is under a header F"n", or F1, F2, F3, etc. out to F900.

Here is the alteration to the code: This ran, I'm working to see if it did what I wished.

filename rename temp;
data _null_;
Set HEADER_FILE end=eof;
length oldname Name_Convention $32;
oldname = cats('F',_n_);
file rename;
if _n_ = 1 THEN PUT Name_Convention;
put oldname=Name_Convention;
if eof then put ';';
RUN;

While it seemed to run and if I replaced _null_ with the an output table name, the resulting data looked correct... (two variables, one with my desired header names, the other with variable "oldname" looking like F1 - F900... I then proceeded with

DATA fixed;
set Set_1;
%include rename ;
run;

It ran, but did not complete came up with ERROR 180-322: Statement is not valid or it is used out of proper order.

I the length of "Name Convention" is also already set so I am unable to apply format $32.

Re: Provided Headers vs. Provided Dataset without Headers

Tom — Thu, 12 Nov 2015 22:23:41 GMT

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.

Re: Provided Headers vs. Provided Dataset without Headers

Tom — Thu, 12 Nov 2015 23:39:56 GMT

You need to change:

if _n_ = 1 THEN PUT Name_Convention;

if _n_ = 1 THEN PUT 'RENAME';

So that it will write the text RENAME to the file. This the beginning of the RENAME command.

You need to change:

put oldname=Name_Convention;

put oldname '=' Name_Convention;

so that the equal sign is written to the file. By not quoting the text literal you have asked SAS to write the variable OLDNAME using named format. So of oldname was 'F1' and Name_Convention was 'ID' it would end up witing something 'OLDNAME=F1 ID' to the file instead of 'F1=ID'.

If you want you can run this step to read in the file you created and dump it to the log so you can see what is in it.

data _null_;
   infile rename ;
   input;
   put _infile_;
run;

Re: Provided Headers vs. Provided Dataset without Headers

GalacticAbacus — Fri, 13 Nov 2015 16:59:41 GMT

Hi Freelance!

I gravitated toward Tom's solution because I really enjoy learning exactly why and how something works before I apply it, and while I'm grateful for your taking the time to write the macro solution, it looked a little beyond my current abilities to undertand. I am grateful for the example thought.

Re: Provided Headers vs. Provided Dataset without Headers

GalacticAbacus — Fri, 13 Nov 2015 18:34:11 GMT

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;

Re: Provided Headers vs. Provided Dataset without Headers

Tom — Fri, 13 Nov 2015 19:09:02 GMT

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
;

Re: Provided Headers vs. Provided Dataset without Headers

GalacticAbacus — Mon, 16 Nov 2015 14:30:57 GMT

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.