Macro for every columns

[Anastasija98]

Hello SAS Buddies 🙂

Could you be so polite and help me with small issue? I try to make a new dataset for every columns from my basic dataset. It's my basic dataset with Standardize Data and I would like to find outlier for every columns (like make a flag).

I would like to have someting like it;

I tried do it with below code;

proc sql;
create table zbiory.name as 
    select NAME
    into     :NAMES separated by ' ' 
    from (select name from dictionary.columns
where libname = 'WORK'
and memname='STNDSTANDARDIZED_0000'
and( lower(name) not like '%_ciev_all' and lower(name) not like '%_csev_all' and lower(name) not like 'default%' and lower(name) not in ('period', 'cid', 'app_char_marital_status', 'app_char_job_code',
'app_char_home_status', 'app_char_city', 'app_char_cars', 'act_cus_loan_number')));
    
quit; 


%macro rekordy2(input);
ods results off;
%let N_NAMES = %sysfunc(countw(&NAMES.)); %do i=1 %to &N_NAMES;
%let zmienna=%scan(&NAMES, &i,%STR( ));

data zbiory.outlier&zmienna.;
set &input.;
if  &zmienna. >3 or &zmienna.  <-3 then &zmienna. =1;
else &zmienna. =0;
keep period act_age; 
%end;
ods results on;
%mend;
%rekordy2(STNDSTANDARDIZED_0000);

But it's doesn't work :(.

thank you in advance for your help

[Kurt_Bremser]

„Doesn‘t work“ tells us near to NOTHING.
Describe clearly where the result does not meet your expectations, and post the log by copy/pasting it into a window opened with the </> button.

[Anastasija98]

When I run macro;

proc sql;

    select NAME
    into     :NAMES separated by ' ' 
    from (select name from dictionary.columns
where libname = 'WORK'
and memname='STNDSTANDARDIZED_0000'
and( lower(name) not like '%_ciev_all' and lower(name) not like '%_csev_all' and lower(name) not like 'default%' and lower(name) not in ('period', 'cid', 'app_char_marital_status', 'app_char_job_code',
'app_char_home_status', 'app_char_city', 'app_char_cars', 'act_cus_loan_number')));
    
quit; 

/*Z DATA SETEP*/
/*?*/
/***************tabele z rekordami****************/
%macro rekordy2(input);

%let N_NAMES = %sysfunc(countw(&NAMES.)); 

%do i=1 %to &N_NAMES;
%let zmienna=%scan(&NAMES, &i,%STR( ));

data zbiory.outlier&zmienna.;
set &input.;
if  &zmienna. >3 or &zmienna.  <-3 then &zmienna. =1;
else &zmienna. =0;
keep period &zmienna.; 
%end;
%mend;
 

%rekordy2(STNDSTANDARDIZED_0000);

I have this error;

42  
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "REKORDY2".
42 data zbiory.outlier&zmienna.; set &input.; if &zmienna. >3 or &zmienna. <-3 then &zmienna. =1; else &zmienna. =0;
___
180
42 ! keep period &zmienna.;
ERROR 180-322: Statement is not valid or it is used out of proper order.

For some variables macro works but some no and I don't undestand where there's bug

[PaigeMiller]

Show us the ENTIRE log, not just the error messages. Since this involves a macro, please run this command

options mprint;

then edit the macro to loop once

%do i=1 %to 1;

and re-run the macro and then copy and paste the ENTIRE log into the window that appears when you click on the </> icon

[Tom]

Did you perhaps accidentally set the VALIDVARNAME option to ANY?

If so then you need to modify the code to allow for names that have spaces in them by using the NLITERAL() function when building this list.  Which means you should use something other than space as the delimiter.  

...
select nliteral(NAME) into :NAMES separated by '|' 
...
%sysfunc(countw(&names,|))
...
%scan(&names,&i,|)
...

If you do want to use space as the delimiter than add the Q modifier to the %SCAN() and COUNTW() function calls.

...
select nliteral(NAME) into :NAMES separated by ' ' 
...
%sysfunc(countw(&names,%str( ),q))
...
%scan(&names,&i,%str( ),q)

[ErikLund_Jensen]

Hi @Anastasija98 

sorry, hit the wrong button.

You aren't making things easy. It is a bit difficult to answer your question when you don't provide input data in a usable form and don't explain what you are trying to achieve.

By reading to decipher your code, it seems that you want a boolean flag set for each numeric variable in your input, and set it to 1 if the absolute value is > 3 or else 0. IF this is the case (we cannot know, because we don't know all variables in input), there might be a simpler solution. The following code computes all the boolean variables and adds them to the output data set. Then you have them all in one place and can later select what you want for a given purpose.

data have;
  ID = '12345'; var_1 = 0.234; var_2 = -4; var_3 = -2.9; var_4 = 25;
run;

data _null_; set have (obs=1);
  array vlist _numeric_;
  length out_list $200;
  retain out_list;
  do i = 1 to dim(vlist);
    out_list = catx(' ', out_list, catt('out_',vname(vlist{i})));
  end;
  call symputx('out_list', out_list);
run;
%put &=outline_list;

data want (drop=i); set have;
  array vlist _numeric_;
  array outline_list &out_list;
  do i = 1 to dim(vlist);
    if abs(vlist{i}) > 3 then outline_list{i} = 1; 
    else outline_list{i} = 0;
  end;
run;