topic Macro for every columns in SAS Programming

Macro for every columns

Anastasija98 — Sun, 15 Jan 2023 08:33:49 GMT

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

Re: Macro for every columns

Kurt_Bremser — Sun, 15 Jan 2023 09:25:10 GMT

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

Re: Macro for every columns

ErikLund_Jensen — Sun, 15 Jan 2023 09:32:50 GMT

Re: Macro for every columns

Anastasija98 — Sun, 15 Jan 2023 09:57:42 GMT

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

Re: Macro for every columns

ErikLund_Jensen — Sun, 15 Jan 2023 10:12:30 GMT

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;

Re: Macro for every columns

PaigeMiller — Sun, 15 Jan 2023 11:04:36 GMT

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

Re: Macro for every columns

Tom — Sun, 15 Jan 2023 19:22:53 GMT

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)