topic Re: Names of variables that are written with periods (.) are replaced by hyphens (_). in SAS Programming

Names of variables that are written with periods (.) are replaced by hyphens (_).

Gick — Tue, 03 Oct 2023 09:13:00 GMT

Hi,

Here is an extract from my SAS table. I want the names of variables that are written with periods (.) to be replaced by hyphens (_).

Can someone help me please.

Thank you in advance for your help.

Gick

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Astounding — Tue, 03 Oct 2023 09:18:00 GMT

First step: run a PROC CONTENTS on the data set.

This will verify whether the names that you are looking at are actual variable names, rather than variable labels.

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Gick — Tue, 03 Oct 2023 10:09:39 GMT

/*These are the real names of the variables and I have several like that.
Here is the data extract*/
data BDD_recod;
input Nom $ sexe $ "2 a 3"n classe_age $ Pond organe "10 à 25 ans"n "Q30.A"n $ "Q35bis.A"n $ Q4_91;
datalines;
Jean Masculin 1 0-4ans 10572.50 21 0 f g 1
Marie Feminin 0 5-14ans 10572.50 2 . i k 1
Pierre Masculin 1 15-24ans 10572.5 0 . K S 0
J Feminin 0 0-4ans 10572.50 7 1 E es 0
M Feminin 0 5-14ans 10572.50 6 1 df cit 9
Pe Masculin 0 0-4ans 10572.50 90 1 cdf cd 8
Je Masculin 1 15-24ans 10572.50 32 1 df cd 7
Ma Masculin 1 0-4ans 10572.50 2 0 qh cre cd 4
Pire Feminin . 25-30ans 10572.5 10 1 sx cio 2
Mfir Feminin 0 35-40ans 10572.50 2.1 0 x vt 1
Pell Masculin . 40-45ans 10572.50 4.53 1 nb vr 10
Jeurs Masculin 0 15-24ans 10572.50 8 0 ij c 11
Mam Masculin 1 0-4ans 10572.50 56 1 jor b 12
Piress Feminin 1 15-24ans 10572.5 21.30 0 fr a 14
;

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Gick — Tue, 03 Oct 2023 10:23:15 GMT

This database is created manually. The real base sent to me is as presented in the image with several variables written with dots (.)

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Gick — Tue, 03 Oct 2023 10:32:58 GMT

Here is the message I get "ERROR: Variable Q35bis not found. ERROR: Variable A not found." because SAS does not recognize Q35bis variables. This is why I want to transform these variables with point(.) by (_), that is to say Q35bis.A becomes Q35bis_A. In fact SAS separates these variables into 2.

proc sql;
select name into :var_list separated by ' '
from dictionary.columns
where libname = "WORK" and memname = "upcase(BDD_FINALE_POND)" and
not prxmatch('/^\d/', name);
quit;

%put &var_list.;

%macro compare_freq_means(data=, weight_var=);

%do i = 1 %to %sysfunc(countw(&var_list.));
%let current_var = %scan(&var_list., &i.);

%let var_type = %sysfunc(vartype(&data., &current_var.));
%put var_type=&var_type.;

%if &var_type. = 2 %then %do;
proc freq data=&data.;
tables &current_var.;
title "Résultats pour &current_var. sans pondération";
run;
%end;

%else %do;
proc means data=&data.;
var &current_var.;
title "Résultats pour &current_var. sans pondération";
run;
%end;

%if &var_type. = 2 %then %do;
proc freq data=&data.;
tables &current_var.;
weight &weight_var.;
title "Résultats pour &current_var. avec pondération";
run;
%end;
%else %do;
proc means data=&data.;
var &current_var.;
weight &weight_var.;
title "Résultats pour &current_var. avec pondération";
run;
%end;
%end;
%mend;

%compare_freq_means(data=BDD_recod, weight_var=Pond);

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

PaigeMiller — Tue, 03 Oct 2023 10:54:00 GMT

@Gick

As requested above by @Astounding , show us the PROC CONTENTS on this SAS data set.

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Kurt_Bremser — Tue, 03 Oct 2023 11:00:06 GMT

Funny strings like that should never (as in NEVER) be variable names, such stuff belongs in labels. Name literals (especially extremely stupid name literals like that) make your work unnecessarily hard.

We programmers have a technical term for people who make their work deliberately and unnecessarily hard. We call them "idiots".

What is your real data source? I've never seen a real DBA (database administrator) who would have crazy stuff like this as column names. So I guess you get some noob's Excel file. If you import that with

options validvarname=v7;

you get easy-to-handle variable names OOTB, and the nice strings are automatically used as labels IIRC.

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

ballardw — Tue, 03 Oct 2023 12:35:42 GMT

I do not see any VARIABLE NAMES "written with a period" in that picture. I see some VALUES.

If you mean to display the value of Q4_91 that currently shows as . where Nom='Ma' then change your system option for missing:

options missing='_';

That will change the SAS default behavior of showing missing values from . to _ ;

@Gick wrote:

Hi,

Here is an extract from my SAS table. I want the names of variables that are written with periods (.) to be replaced by hyphens (_).

Can someone help me please.

Thank you in advance for your help.

Gick

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Quentin — Tue, 03 Oct 2023 13:14:11 GMT

You can use the RENAME option to rename the variables, e.g.:

options validvarname=any ;

data have ;
 "Q30.a"n=1 ;
run ;

data want ;
  set have (rename=("Q30.a"n=Q30_a));
run ;

options validvarname=v7 ;

If you have a lot of problematic variable names, you can use additional code to generate the list of rename pairs.

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

PaigeMiller — Tue, 03 Oct 2023 13:24:52 GMT

Renaming the variables is a good idea so you won't have to keep typing the difficult-to-type variable names. Adding labels is also a good thing to do in this situation if you absolutely have to maintain these difficult to type names for output legibility. I would do it like this:

proc datasets library=work nolist;
    modify bdd_recod;
    rename "Q30.a"n=Q30_a;
    label q30_a='Q30.a';
run;
quit;

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

ballardw — Tue, 03 Oct 2023 14:36:52 GMT

@Gick wrote:
This database is created manually. The real base sent to me is as presented in the image with several variables written with dots (.)

At least you aren't saying variable names with dots.

That is a SAS system option. The default is to display MISSING values as a dot. You can control what character is displayed using the system option MISSING=. Pick any character. You can make a missing value appear as any single ASCII or EBCDIC (IBM Mainframe) your system supports.

You can check on the setting by running this code:

proc options option=missing;run;

Almost certainly your LOG will show something similar to the following: (your SAS version and line numbers likely to differ)

To change that behavior regardless of who built the data set, who sent it or what ever you use the OPTIONS statement to set the appearance YOU want for MISSING values.

It is an actual useful feature. When the recipient of a report expects to see 0 then I use options missing='0'. There are times that a blank makes sense in context. Even had use for treating missing values as |. None of those affect the actual value stored in the data set.

293  proc options option=missing;run;

    SAS (r) Proprietary Software Release 9.4  TS1M4

 MISSING=.         Specifies the character to print for missing numeric
                   values.
NOTE: PROCEDURE OPTIONS used (Total process time):

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Reeza — Tue, 03 Oct 2023 15:10:07 GMT

@Gick wrote:
Here is the message I get "ERROR: Variable Q35bis not found. ERROR: Variable A not found." because SAS does not recognize Q35bis variables. This is why I want to transform these variables with point(.) by (_), that is to say Q35bis.A becomes Q35bis_A. In fact SAS separates these variables into 2.

proc sql;
select nliteral(name) into :var_list separated by '|'
from dictionary.columns
where libname = "WORK" and memname = "upcase(BDD_FINALE_POND)" and
not prxmatch('/^\d/', name);
quit;

%put &var_list.;

%macro compare_freq_means(data=, weight_var=);

%do i = 1 %to %sysfunc(countw(&var_list., |));
%let current_var = %scan(&var_list., &i., |);

When you use the type of notation with periods and spaces you need to use NLITERAL function when selecting the names of the variables.

You may also want to change the separator to be a more defined character otherwise, I believe it's a space. And since you have spaces in your variable names this will have things show up as multiple words.

So add a delimiter to your variables and to the COUNTW and SCAN() functions as well

What do you mean by the data set is created manually? If by data step as below, feel free to change it in the text directly.

/*These are the real names of the variables and I have several like that.
Here is the data extract*/
data BDD_recod;
input Nom $ sexe $ _2_a_3 classe_age $ Pond organe _10_a_25 Q30_A $ Q35bis_A $ Q4_91;
datalines;

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Tom — Tue, 03 Oct 2023 16:49:43 GMT

So someone sent you a dataset with those strange variable names?

Can you just ask them to create valid variable names, since it is their data it should be easier for them to make the decisions about what names are appropriate. They can use the LABEL to store the strange characters.

If you really must fix it then you should be able to just create a macro variable with OLD=NEW name pairs needed by the RENAME statement or the RENAME= dataset option. You could store the original name into the label.

I like to replace multiple adjacent nonstandard characters with a single _ to make the resulting names easier to read and type.

proc contents data=HAVE noprint out=contents;
run;

data names;
  set contents;
  newname=translate(trim(name),' ',compress(name,,'ad'));
  newname=translate(strip(compbl(newname)),'_',' ');
  if '0' <= char(newname,1) <='9' then newname='_'||newname;
  keep name newname;
run;  

proc sql noprint;
%let renames=;
%let labels=;
select catx('=',nliteral(name),newname)
     , catx('=',newname,quote(trim(name),"'"))
  into :renames separated by ' '
     , :labels separated by ' '
from names
where upcase(name) ne upcase(newname)
;
quit;

%put &=sqlobs ;
%put &=renames;
%put &=labels;

data want;
  set have(rename=(&renames));
  label &labels;
run;

Let's make an example dataset and try it.

options validvarname=any;
data have;
  id=1;
  'q30.a'n=2;
  '123+-45&67'n=3;
run;

Results

70
71   %put &=sqlobs ;
SQLOBS=2
72   %put &=renames;
RENAMES='123+-45&67'N=_123_45_67 "q30.a"N=q30_a
73   %put &=labels;
LABELS=_123_45_67='123+-45&67' q30_a='q30.a'
74
75   data want;
76     set have(rename=(&renames));
77     label &labels;
78   run;

NOTE: There were 1 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


79   proc contents varnum;
80   run;

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.05 seconds
      cpu time            0.03 seconds

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Gick — Wed, 04 Oct 2023 06:30:39 GMT

Hi,
Well, I don't know what to say. The SAS table that I proposed to you is made manually (I adapted it to the table that I actually have to clearly explain my concern) but in reality, my SAS data is provided as I sent you in the image.

I have several variables like this.

I know very well that no one will write these variable names like this. Which doesn't prevent me from being sent a SAS table like that. The idea is to try to see if anyone could offer me an approach.

THANKS

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Gick — Wed, 04 Oct 2023 06:56:50 GMT

The code is perfect except that the "want" table created has no observations.

Thank you
Gick

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Gick — Wed, 04 Oct 2023 07:30:06 GMT

/*Here my proposition of code*/

%macro remplacer_points(table_in, table_out);

data &table_out;
set &table_in;

array old_vars(*) _numeric_;
array new_vars(*) $ _temporary_;

do i = 1 to dim(old_vars);
/* Have Names variable */
var_name = vname(old_vars[i]);

/* Remplace (.) by (_) */
new_vars[i] = tranwrd(var_name, '.', '_');
end;

do i = 1 to dim(old_vars);
if not missing(new_vars[i]) then
rename old_vars[i]=new_vars[i];
end;

drop i var_name;
run;
%mend;

%remplacer_points(table_in=BDD_recod, table_out=BBB);

Re: Names of variables that are written with periods (.) are replaced by hyphens (_).

Gick — Wed, 04 Oct 2023 08:19:14 GMT

/*Another code*/
%macro remplacer_points(table_in, table_out);

proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where libname = upcase(libname) and memname = upcase("&table_in");
quit;

data &table_out;
set &table_in;

%let num_vars = %sysfunc(countw(&varlist));
%do i = 1 %to &num_vars;
/* Obtenir le nom de la variable */
%let var_name = %scan(&varlist, &i);
%put &var_name.;

%let new_var_name = %sysfunc(tranwrd(&var_name, '.', '_'));
%put &new_var_name.;

rename &var_name = &new_var_name;
%end;
run;
%mend; Always the same