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 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 1571 views
  • 3 likes
  • 5 in conversation