BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Gick
Pyrite | Level 9
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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

 

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;

 

 

 

 

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Gick
Pyrite | Level 9
Of course. even I rename first but there are several (more than 100 variables) like that
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller
Gick
Pyrite | Level 9
However the code returns the results to me several times. In fact, as many variables, as many results are repeated.
PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
Reeza
Super User

 

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;

 

 

 

 

Tom
Super User Tom
Super User

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;
...

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1920 views
  • 3 likes
  • 5 in conversation