SAS proc sql - how to read in log of variable but retain the variable's label

1
votes

I am reading in variables from a few different datasets using proc sql, which I am trying to improve on.

What I'd like to do is read in a variable from a dataset using proc sql, but take the log of the variable as it's read in, but keep the variable name.

PROC SQL;
  CREATE TABLE all AS
  SELECT DISTINCT a.*,
                  b.var1, LOG(b.var2) AS log_var2                    
  FROM pop AS a
  INNER JOIN trt AS b
    ON a.study=b.study AND a.subj=b.subj
  ;
QUIT;

The above creates a table with the variable log_var2, but it does not have the variable name from var2. Is there a way to keep this? The idea is that the label is used later on when transposing and the label is used as values within a table, but var2 itself may change so I need a robust method of labelling log_var2 with the label name from var2.

Any ideas?

Thanks in advance

2
sqlsaslabel
Just to clarify: I'm pretty sure you want the variable label from var2. The variable label is what it looks like by default when you open the dataset, but can be different from the variable name, which is what you use to refer to it in a program. – Joe

2 Answers

4
votes

As far as I know, there is not a way of persisting the variable label automatically in proc sql. Unfortunately, you don't have access to the vlabel etc. functions, and even if you did the timing would be wrong to use them.

In addition to defining the label manually as yukclam9 shows, you can obtain that value using variable information functions, or perhaps more easily in a data step beforehand. This would be handy if you don't want to hard-code the value in the program.

data _null_;
  set trt;
  vlabel_Var2 = vlabel(var2); *assign value to a variable;
  call symputx('vlabel_var2',vlabel_var2); *assign to macro variable;
  stop;  *only process one line;
run;

proc sql;
  CREATE TABLE all AS
  SELECT DISTINCT a.*,
                  b.var1, LOG(b.var2) AS log_var2 label="Log of &vlabel_var2."                 
  FROM pop AS a
  INNER JOIN trt AS b
    ON a.study=b.study AND a.subj=b.subj
  ;
QUIT;

You could of course keep out the "Log of" part if you want just the original label, it's just included to show you could also add that.

1
votes
PROC SQL;
CREATE TABLE all AS
SELECT DISTINCT a.*,
              b.var1, LOG(b.var2) AS var2                    
FROM pop AS a
INNER JOIN trt AS b
ON a.study=b.study AND a.subj=b.subj;
QUIT;

by this you would do the log operation and return the value to a variable named var2. or did i misunderstand you case?

if you want the label particular you could do 

log(b.var2) as var2 label=" you named it"