topic Re: MACRO DO LOOP in SAS Data Management

MACRO DO LOOP

sumsar — Fri, 11 Jan 2019 10:15:14 GMT

Hi everyone, I meet a problem with a do loop.

I try to modify all the format of my dates variables in one time.

I have put all my dates variables in a list and I keep the length of the list too with this instruction :

proc contents data=table out=var;run;

proc sql;
select name into :var_list separated by " " from var where format="IS8601DT";
select count(*) into :nb from var where format="IS8601DT";
quit;

%let test = &var_list;
%let nb = &nb;

And then, I try to modify the format of the dates but when I make it variable by variable it works well , like this :

data t;
set table ;
%scan(&test,1)=datepart(%scan(&test,1));
format %scan(&test,1) DDMMYY10.;
informat %scan(&test,1) DDMMYY10.;

%scan(&test,2)=datepart(%scan(&test,2));
format %scan(&test,2) DDMMYY10.;
informat %scan(&test,2) DDMMYY10.;

%scan(&test,3)=datepart(%scan(&test,3));
format %scan(&test,3) DDMMYY10.;
informat %scan(&test,3) DDMMYY10.;

%scan(&test,4)=datepart(%scan(&test,4));
format %scan(&test,4) DDMMYY10.;
informat %scan(&test,4) DDMMYY10.;

%scan(&test,5)=datepart(%scan(&test,5));
format %scan(&test,5) DDMMYY10.;
informat %scan(&test,5) DDMMYY10.;

%scan(&test,5)=datepart(%scan(&test,5));
format %scan(&test,5) DDMMYY10.;
informat %scan(&test,5) DDMMYY10.;

%scan(&test,5)=datepart(%scan(&test,5));
format %scan(&test,5) DDMMYY10.;
informat %scan(&test,5) DDMMYY10.;

%scan(&test,6)=datepart(%scan(&test,6));
format %scan(&test,6) DDMMYY10.;
informat %scan(&test,6) DDMMYY10.;

%scan(&test,7)=datepart(%scan(&test,7));
format %scan(&test,7) DDMMYY10.;
informat %scan(&test,7) DDMMYY10.;

%scan(&test,8)=datepart(%scan(&test,8));
format %scan(&test,8) DDMMYY10.;
informat %scan(&test,8) DDMMYY10.;

%scan(&test,9)=datepart(%scan(&test,9));
format %scan(&test,9) DDMMYY10.;
informat %scan(&test,9) DDMMYY10.;

%scan(&test,10)=datepart(%scan(&test,10));
format %scan(&test,10) DDMMYY10.;
informat %scan(&test,10) DDMMYY10.;

%scan(&test,11)=datepart(%scan(&test,11));
format %scan(&test,11) DDMMYY10.;
informat %scan(&test,11) DDMMYY10.;

%scan(&test,12)=datepart(%scan(&test,12));
format %scan(&test,12) DDMMYY10.;
informat %scan(&test,12) DDMMYY10.;

run;

But when I try to introduce a do loop, it doesn't work :

data t;
set table;
do i=1 TO &nb;
%scan(&test,i)=datepart(%scan(&test,i));
format %scan(&test,i) DDMMYY10.;
informat %scan(&test,i) DDMMYY10.;
end;
run;

I have this error message : "A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: i"

Could someone help me please?

I'm sorry for my low level I start in SAS!

Re: MACRO DO LOOP

VDD — Fri, 11 Jan 2019 11:14:53 GMT

do you need ampersands in front of your I's?

data t;
set table;
do i=1 TO &nb;
%scan(&test,&i)=datepart(%scan(&test,&i));
format %scan(&test,&i) DDMMYY10.;
informat %scan(&test,&i) DDMMYY10.;
end;
run;

Re: MACRO DO LOOP

sumsar — Fri, 11 Jan 2019 11:17:39 GMT

Hi and thank you for your answer.

Still doesn't work, I have this in my log :

WARNING: Apparent symbolic reference I not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: &i
ERROR: Argument 2 to macro function %SCAN is not a number.
ERROR 180-322: Statement is not valid or it is used out of proper order.
ERROR 22-322: Erreur de syntaxe, l'une des valeurs suivantes est attendue : un nom, _ALL_,
_CHARACTER_, _CHAR_, _NUMERIC_."

Re: MACRO DO LOOP

PaigeMiller — Fri, 11 Jan 2019 11:49:55 GMT

run this line of code at the start of your program

options mprint mlogic symbolgen;

then paste the entire SASLOG into the window that appears when you click on the {i} icon

By the way, as written, you do not have a "macro do loop" as your title implies, you have a data step do loop which uses a macro function. This is most likely the problem, you have to be very careful mixing and matching macro and data step functionality. I think you probably need a macro do loop (not a data step do loop) and you do need &i instead of i, but the LOG will clear all of this up.

Re: MACRO DO LOOP

Kurt_Bremser — Fri, 11 Jan 2019 11:55:14 GMT

You need to use a macro do loop:

data t;
set table;
%do i=1 %to &nb;
%scan(&test,&i)=datepart(%scan(&test,&i));
format %scan(&test,&i) DDMMYY10.;
informat %scan(&test,&i) DDMMYY10.;
%end;
run;

But you can do it directly off sashelp.vtable with call execute():

data _null_;
set sashelp.vcolumn (
  where=(libname='WORK' and memname='TABLE' and format = 'IS8601DT')
) end =eof;
if _n_ = 1 then call execute('data t; set table;');
call execute(name !! '=datepart(' !! name !! ');');
call execute('format ' !! name !! ' ddmmyy10.;');
if eof then call execute('run;');
run;

Re: MACRO DO LOOP

PaigeMiller — Fri, 11 Jan 2019 11:55:54 GMT

Also, as far as I can see, macros are completely unnecessary here, and only complicates the whole thing. You could create an array in your data step, and do all of this without macros.

Re: MACRO DO LOOP

sumsar — Fri, 11 Jan 2019 12:50:05 GMT

It works perfectly!

Thank you so much!

Re: MACRO DO LOOP

sumsar — Fri, 11 Jan 2019 13:42:07 GMT

Just a last question : I have tried to create a macro to do the job on all my files :

proc sql;
select distinct memname into :liste separated by " " from sashelp.vcolumn where libname="xxx";
select count (distinct memname) into :nb from sashelp.vcolumn where libname="xxx";
quit;

%macro date;
%do i=1 %to &nb;
data _null_;
set sashelp.vcolumn (
where=(libname="xxx" and memname=%scan(&liste,&i) and format = "IS8601DT19.")
) end =eof;
if _n_ = 1 then call execute('data xxx.%scan(&liste,&i); set xxx.%scan(&liste,&i);');
call execute(name !! '=datepart(' !! name !! ');');
call execute('format ' !! name !! ' ddmmyy10.;');
if eof then call execute('run;');
run;
%end;
run;
%mend;

But the log says that my files are not present in VCOLUMN, could you help me please?

Re: MACRO DO LOOP

Kurt_Bremser — Fri, 11 Jan 2019 13:54:03 GMT

Given one of your datasets is named YYY, the where condition in the macro would resolve to this:

where=(libname="xxx" and memname=YYY and format = "IS8601DT19.")

Since there is no variable YYY in sashelp.vtable, the code will throw an ERROR.

You have to create a string:

where=(libname="xxx" and memname="%scan(&liste,&i)" and format = "IS8601DT19.")

In your further code, take care to use double quotes when macro variables have to be resolved:

if _n_ = 1 then call execute("data xxx.%scan(&liste,&i); set xxx.%scan(&liste,&i);");

Re: MACRO DO LOOP

sumsar — Fri, 11 Jan 2019 14:04:22 GMT

Thank you so much !

Re: MACRO DO LOOP

Tom — Fri, 11 Jan 2019 14:10:54 GMT

Don't use macro code when there is regular SAS code that can do the job.

In this case you want to use an ARRAY.

So first get your list of variables.

proc sql noprint;
select name
  into :var_list separated by " " 
  from var 
  where format="IS8601DT"
;
%let nb=&sqlobs;
quit;

Then you can use your list to define the array and sets the new formats.

data t;
  set table ;
  array dates &var_list ;
  do _n_=1 to dim(dates);
     dates(_n_)=datepart(dates(_n_));
  end;
  format &var_list DDMMYY10.;
  informat &var_list DDMMYY10.;
run;

Re: MACRO DO LOOP

sumsar — Fri, 11 Jan 2019 14:29:07 GMT

Thank you, it works well too.

I have another thing I want to do, I don't know if I can use the same post.

In all my dataset, I have some variables '_decode' which correspond to the format of my variable.

For example, in adataset, I have a variable var1 which takes the values 0 and 1 and just on the right, I have a variable var1_decode which takes the values No and Yes.

I would like to put the values No and Yes in my variable var1 and to delete the variable var1_decode.

And that for all the variables which contain "_decode" in their name.

Do you see a mean to do that ?

Thank you.

Re: MACRO DO LOOP

Tom — Fri, 11 Jan 2019 14:35:02 GMT

Make a new post. Or just the google CNTLIN= option on PROC FORMAT.

Re: MACRO DO LOOP

Kurt_Bremser — Mon, 14 Jan 2019 07:53:15 GMT

You have to check your where condition, if it can find any matches at all. Does your format on its own return any results? I ask because I can't find documentation for a IS8601DT format.

And no macro is needed, by group processing in the data step will do it:

%let libname=XXX;
%let dateformat=E8601DT19.;

data _null_;
set sashelp.vcolumn (
where=(libname="&libname." and format = "&dateformat.")
);
by memname;
if first.memname then call execute("data &libname.." !! strip(memname) !! "; set &libname.." !! strip(memname) !! ';');
call execute(name !! '=datepart(' !! name !! ');');
call execute('format ' !! name !! ' ddmmyy10.;');
if last.memname then call execute('run;');
run;