topic Re: Rename variables with points or propose another calculation approach in SAS Programming

Rename variables with points or propose another calculation approach

Gick — Wed, 25 Oct 2023 15:03:27 GMT

data MaTable;
   input Nom $ "A.D.C_EF"n $ Score_biomarqueur Score_pesticides "Q13t"n $ "Q13bist"n $ "Q1.A"n $ "Qt.2"n $;
   datalines;
   Doe John 30 1 oui o T i
   Smith Jane 25 20 oui n T o
   Brown Alice 35 2 non n T o
   A C 2 2 non n K j
   A C 5 23 non n K j
   B John 10 21 non n K l
   K C 31 13 oui o K l
   B Jane 21 4 non o S l
;
run; 

%macro stat_central();
proc sql ;
   select name into :char_vars separated by ' ' 
   from dictionary.columns where libname='WORK' and memname="MATABLE" and type='char';
quit;  

%put &char_vars.;  
%do k=1 %to %sysfunc(countw(&char_vars.));
    %let char = %scan(&char_vars., &k.); 
	proc sort data=MATABLE;
	by "&char."n;
	run;
	proc means data=MATABLE;
var Score_biomarqueur Score_pesticides;
class "&char."n;
run;
%end; 
%mend;  

%stat_central();

Hi,

Here is an extract from my table. I want to run this macro program but I get an error message because of the variables having dots. In fact, these variables separate into 2 or more variables. As a result, SAS no longer finds them in the base.

Can anyone help me solve this problem or suggest another approach?

Note that this is just an extract from the table. I have several variable names written with periods.

Best regards,
Gick

Re: Rename variables with points or propose another calculation approach

PaigeMiller — Wed, 25 Oct 2023 15:22:00 GMT

You need to tell the macro to split words using the space character.

%do k=1 %to %sysfunc(countw(&char_vars.,%str( )));
    %let char = %scan(&char_vars., &k.,%str( ));

Why a macro anyway? PROC MEANS/PROC SUMMARY can split the data by each class variable without macros, which also has the benefit of passing through the data only once, which might make a difference if you have very large data sets.


proc sql ;
   select cats(quote(trim(name),"'"),'n') into :char_vars separated by ' '
   from dictionary.columns where libname='WORK' and memname="MATABLE" and type='char';
quit;
%put &=char_vars;

proc means data=matable;
    var Score_biomarqueur Score_pesticides;
    class &char_vars;
    ways 1;
run;

In general, I almost always avoid using dots and the like in variable names, if the database forces me to use dots or similar, I will usually rename the variables before I try to do other programming with them.

Re: Rename variables with points or propose another calculation approach

Gick — Wed, 25 Oct 2023 15:29:07 GMT

Of course. even I rename first but there are several (more than 100 variables) like that

Re: Rename variables with points or propose another calculation approach

PaigeMiller — Wed, 25 Oct 2023 16:49:29 GMT

proc sql noprint;
   select cats(quote(trim(name)),'n = ',quote(compress(trim(name),'.')),'n') into :renames separated by ' ' 
   from dictionary.columns where libname='WORK' and memname="MATABLE" and type='char';
quit;
%put &=renames;

proc datasets library=work nolist;
    modify matable;
    rename &renames;
run; quit;

Re: Rename variables with points or propose another calculation approach

Gick — Wed, 25 Oct 2023 15:42:20 GMT

However the code returns the results to me several times. In fact, as many variables, as many results are repeated.

Re: Rename variables with points or propose another calculation approach

Kurt_Bremser — Wed, 25 Oct 2023 16:21:28 GMT

Most simple solution: don't allow these stupid name literals in the first place. Funny strings belong in labels.

Re: Rename variables with points or propose another calculation approach

PaigeMiller — Wed, 25 Oct 2023 16:45:39 GMT

@Gick wrote:
However the code returns the results to me several times. In fact, as many variables, as many results are repeated.

I don't know what this means. Show us the code. Show us the output.

Re: Rename variables with points or propose another calculation approach

Reeza — Wed, 25 Oct 2023 17:38:18 GMT

data MaTable;
    input Nom $ "A.D.C_EF"n $ Score_biomarqueur Score_pesticides 
        "Q13t"n $ "Q13bist"n $ "Q1.A"n $ "Qt.2"n $;
    datalines;
   Doe John 30 1 oui o T i
   Smith Jane 25 20 oui n T o
   Brown Alice 35 2 non n T o
   A C 2 2 non n K j
   A C 5 23 non n K j
   B John 10 21 non n K l
   K C 31 13 oui o K l
   B Jane 21 4 non o S l
;
run;

%macro stat_central();
    proc sql;
        select nliteral(name) into :char_vars separated by '|' from 
            dictionary.columns where libname='WORK' and memname="MATABLE" and 
            type='char';
    quit;

    %put &char_vars.;

    %do k=1 %to %sysfunc(countw(&char_vars., |));
        %let char = %scan(&char_vars., &k., |);
        %put &char.;

        proc sort data=MATABLE;
            by &char.;
        run;

        proc means data=MATABLE;
            class &char.;
            var Score_biomarqueur Score_pesticides;
        run;

    %end;
%mend;

%stat_central();

Use NLITERAL to get the name in a valid naming format.

Use a delimiter that is not a space to delimit the list.

Add the delimiter specification to the COUNTW().

But you can also do this using CLASS and a WAYS statement without the loops and use _character_ to reference character variables.

        proc means data=MATABLE;
            class _character_;
            ways 1;
            var Score_biomarqueur Score_pesticides;
        run;

Re: Rename variables with points or propose another calculation approach

Tom — Thu, 26 Oct 2023 02:11:26 GMT

And you don't need to re-count the number of names.

PROC SQL already counted them for you. so just remember that number.

proc sql;
select nliteral(name)
  into :char_vars separated by '|' 
from dictionary.columns 
where libname='WORK'
  and memname="MATABLE"
  and type='char'
;
%let nvars=&sqlobs;
quit;

%do i=1 %to &nvars;
...