Help using Base SAS procedures

variable looping

Reply
Contributor
Posts: 36

variable looping

Hi.

I have a lot of text formatted time variable and I want to convert them to SAS time and do some calculation. I have a macro which runs well, but it does only one variable at a time. Can I loop this macro over the set of variables I have? It would be great to be able to pick up the names of the variables and then pass each name over to the macro.

The following works for manual input of data set variable in the macro arguments.

data set temp;
set source;
%times(start1, finish1, calcduration1);
run;
SAS Super FREQ
Posts: 8,862

Re: variable looping

Posted in reply to blackraven
Hi:
There are several ways to accomplish what you want. But without knowing what statements are being generated by the %times() macro program, it is a little iffy to speculate on the correct approach. The design of %times could have an impact on how you would call the macro program iteratively.

Also, I was a bit confused by your code...you show:
[pre]
data set temp;
[/pre]
Are you really creating 2 SAS datasets: WORK.SET and WORK.TEMP??? Does your macro program %times write to one or the other, depending on some logic????

Here are some questions to think about:
--- Are all the variables you want to process in SET SOURCE????
--- Are all the variables you want to process numbered variables?? START1, START2, START3, FINISH1, FINISH2, FINISH3, etc??? Do the variables have different names: START1, FRED, STVAR, FINISH1, ETHEL, FNVAR???
--- Does one observation contain all the variables???
--- Do you need to perform this calculation on every observation in the input dataset? Or do you need to conditionally perform this calculation?

One approach you might try to is determine whether multiple calls to the %times macro program give the results you desire in WORK.TEMP:
[pre]
data temp;
set source;
%times(start1, finish1, calcduration1);
%times(fred, ethel, calcduration2);
%times(stvar, fnvar, calcduration3);
%times(start4, finish4, calcduration4);
run;
[/pre]

If a test like the above works, and you get the desired results, then a simple data step DO loop and an ARRAY might work for your purposes.
If multiple calls to the %times macro program does NOT work to produce the results you want, then you may have to delve into what is happening in the %times macro in order to accomplish what you want.

On the other hand, if you want to vary the name of the output dataset or the input dataset, as well as the variable names you want to process, you might wish to
use a Macro %DO loop inside a new Macro program to generate multiple output datasets from multiple input datasets.

I always recommend this paper as a good starting point to understand, in general, how macro variables work and how macro programs work:
http://www2.sas.com/proceedings/sugi28/056-28.pdf
It should give you some ideas on macro basics. Then this blog posting on SAS Macro processing has a very simple example of a %DO loop that might be useful:
http://scott.sherrillmix.com/blog/programmer/sas-macros-letting-sas-do-the-typing/

Finally, the code below shows a very, very simple example of using a data step DO loop. Depending on the processing you want to do, this approach may or may not work for you. Note that my macro program, %DOCALC, is only generating 1 line of SAS code for every macro call.

Last, it would be remiss of me not to point out that IF all you want to do is calculate a new variable based on data step manipulations, you might wish to investigate PROC FCMP, which is a new feature of SAS that allows you to write a user-defined function, which you could then call from inside your data step program. If you have SAS 9.2, then PROC FCMP is available to you, as described here:
http://www2.sas.com/proceedings/forum2007/008-2007.pdf

cynthia
[pre]
** Define a Macro program to perform a calculation;
%macro docalc(arg1,arg2,arg3);
&arg3 = &arg1 * &arg2;
%mend docalc;

** Make some data;
data testvars;
infile datalines;
input name $ var1 var2 var3 hvar1 hvar2 hvar3;
return;
datalines;
alan 10 12 14 5 3 2
barb 15 10 5 5 4 3
carl 10 15 3 6 5 4
;
run;

** Call the macro program 3 different times;
data makenew;
length calc1 calc2 calc3 8.;
set testvars;
%docalc(var1, hvar1,calc1);
%docalc(var2, hvar2,calc2);
%docalc(var3, hvar3,calc3);
run;

proc print data=makenew;
title 'Calculate with multiple calls';
var name var1 hvar1 calc1 var2 hvar2 calc2 var3 hvar3 calc3;
run;

** Call the macro program inside a DO loop using ARRAY references;
data makenew_alt;
length calc1 calc2 calc3 8.;
set testvars;
array v var1-var3;
array h hvar1-hvar3;
array c calc1-calc3;
do i = 1 to 3 by 1;
%docalc(v(i), h(i), c(i));
end;
run;

proc print data=makenew_alt;
title 'Calculate using DO Loop and array';
var name var1 hvar1 calc1 var2 hvar2 calc2 var3 hvar3 calc3;
run;
[/pre]
Contributor
Posts: 36

Re: variable looping

Posted in reply to Cynthia_sas
Thanks Cynthia.

The problem is that this kind of loop seems not to work with macros.

This works:

data temp;
set source;
%times(starta, finisha);
%times(startb, finishb);
%times(startc, finishc);
run;

But below does not work. It seems that it is not possible to pass array elements into the macro arguments. SAS complains about syntax error pointing at the first parenthesis in the array, marked ********** below.

data temp;
set source;

array start a b c d;
array finish a b c d;

do i = 1 to dim(start);
%times(start(i), finish(i)); ***************
end;
drop i;
run;
Super Contributor
Posts: 359

Re: variable looping

Posted in reply to blackraven
array start starta startb startc startd;
array finish finisha finishb finishc finishd;


start(1) in your case = 'a' not 'starta'
Contributor
Posts: 36

Re: variable looping

Very true, but this does not help. It is still not possible. The SAS log complains already when reading the macro arguments.

I also tested this without any luck.

data temp;
set source;

array started a b c d e;
array finished a b c d e;
array answered a b c d e;

do i = 1 to dim(started);
%let start = started(i);
%let finish = finished(i);
%let answer = answered(i);
%times(&start, &finish, &answer);
end;
drop i;
run;
Super Contributor
Posts: 359

Re: variable looping

Posted in reply to blackraven
The macro varaibles you are creating do not resolve until after the datastep is done. Use:

data temp;
set source;

array started starta startb startc startd starte;
array finished finisha finishb finishc finishd finishe;
array answered answera answerb answerc answerd answere;

do i = 1 to dim(started);

%times(started(i), finished(i), answered(i));
end;
drop i;
run;

You do not need to use macro variables as the parameters.
Super Contributor
Posts: 359

Re: variable looping

data one;
starta = '10/12/2007';
finisha = '09/01/2008';
startb = '11/12/2007';
finishb = '03/01/2008';
run;

%macro times(s, f, a);
&a = intck('day', input(&s, mmddyy10.), input(&f, mmddyy10.));
%mend times;

data two ;
set one;
drop i; answera = .; answerb = .;
array started starta startb;
array finished finisha finishb;
array answered answera answerb;
do i = 1 to dim(started);
%times(started(i), finished(i), answered(i));
end;
run;
Contributor
Posts: 36

Re: variable looping

Thanks.

I have an error in my macro. When I run it with manual argument input it works, but not when passing array elements to it. The full code is as follows and the idea is to parse two text string dates for each record and then calculate the duration between these times and write a third variable for this record.

I have commented the rows that cannot be run when passing array elements to the macro:

%macro times(startvar, finishvar, answervar);

datetemp = &startvar;
datetemp = cat(scan(datetemp, 3), scan(datetemp, 2), scan(datetemp, -1), '/', scan(datetemp, 4));
datetemp_1 = input(datetemp, datetime20.);
format datetemp_1 datetime20.;
drop datetemp;
*drop &startvar;
*rename datetemp_1 = &startvar;

datetemp = &finishvar;
datetemp = cat(scan(datetemp, 3), scan(datetemp, 2), scan(datetemp, -1), '/', scan(datetemp, 4));
datetemp_01 = input(datetemp, datetime20.);
format datetemp_01 datetime20.;
drop datetemp;
*drop &finishvar;
*rename datetemp_0010 = &finishvar;

*format &answervar TIME10.;
*if datetemp_1 > 0 then &answervar = HMS(hour(datetemp_01 - datetemp_1), minute(datetemp_01 - datetemp_1), second(datetemp_01 - datetemp_1));

%mend times;




data temp;
set source;

array started starta startb startc;
array finished finisha finishb finishc;
array answered answera answerb answerc;

do i = 1 to dim(started);

%times(started(i), finished(i), answered(i));
end;
drop i;
run;
SAS Super FREQ
Posts: 8,862

Re: variable looping

Posted in reply to blackraven
Hi:
I think that you may not need macro variables or a macro program, such as %TIMES at all. From what I surmise, you have CHARACTER variables for
your START and FINISH variables and you want to do the following:
1) make numeric versions of these variables, where the numeric variable is a SAS date/time value
2) find out the duration between the two values
3) drop the old (character) variables from the output dataset
4) keep the new (numeric) variables in the output dataset and the calculated duration.

I think you can do everything with ARRAYS and without any macro variables at all. Consider the program below. Since you didn't show what your character
dates looked like, I made up something vaguely similar to what I think your dates might look like as character variables. Then I use the equivalent
of your CAT/SCAN logic in a DO loop using ARRAY references.

Note that I show 2 different methods to calculate the time between each start/finish pair. One (which results in CALCDUR1-CALCDUR3)
is a simple subtraction. The other method (which results in CT1-CT3) uses your logic.

cynthia
[pre]

data source;
length starta startb startc finisha finishb finishc $22;
infile datalines dsd dlm=',';
input varid $ starta $ startb $ startc $ finisha $ finishb $ finishc $ ;
return;
datalines;
id0001,"2008 mar 16 01:11:10","2008 mar 17 02:22:22","2008 mar 18 03:33:33","2008 mar 16 02:22:20","2008 mar 17 5:55:23","2008 mar 18 04:44:55"
id0002,"2008 jan 26 01:22:22","2008 jan 27 02:22:33","2008 jan 28 03:23:33","2008 jan 26 01:28:33","2008 jan 27 03:33:44","2008 jan 28 05:23:55"
;
run;

data num_dates(drop=starta startb startc finisha finishb finishc i);
set source;

** "BAD" character variables for date/time are being used to create NEW datetime variables;
** which are NUMERIC and can then be subtracted.;
** Two different calculations for duration are being used. They should both result in the same number.;
** My Character variable may not look exactly the same as your character variable for date.;

array start_c $ starta startb startc;
array finish_c $ finisha finishb finishc;
array newstart stdate1-stdate3;
array newfin fndate1-fndate3;
array cdur calcdur1-calcdur3;
array calctm ct1-ct3;

do i = 1 to dim(start_c);
newstart(i) = input(catt(scan(start_c(i), 3), scan(start_c(i), 2), scan(start_c(i), 1), '/', scan(start_c(i), 4,' ')),datetime20.);
newfin(i)= input(catt(scan(finish_c(i), 3), scan(finish_c(i), 2), scan(finish_c(i), 1), '/', scan(finish_c(i), 4,' ')),datetime20.);
cdur(i) = newfin(i) - newstart(i);
calctm(i) = HMS(hour(newfin(i) - newstart(i)), minute(newfin(i) - newstart(i)), second(newfin(i) - newstart(i)));
end;
run;

ods listing close;
ods html file='changedate.html' style=sasweb;
proc print data=source;
title 'What INPUT data set WORK.SOURCE looks like';
run;


proc print data=num_dates;
title 'After converting Character Dates to Numeric Date Times';
title2 'Note that values for calcdur1-calcdur3 are same as other calculation for ct1-ct3';
var stdate1 fndate1 calcdur1 ct1 stdate2 fndate2 calcdur2 ct2 stdate3 fndate3 calcdur3 ct3;
format stdate1- stdate3 fndate1-fndate3 datetime20.
calcdur1-calcdur3 ct1-ct3 time8.;
run;
ods _all_ close;
ods listing;

title; footnote;
[/pre]
Contributor
Posts: 36

Re: variable looping

Posted in reply to Cynthia_sas
Thanks a lot, Cynthia!

Below works great. Now I need to sense the length of the start variable list when defining the duration times. I also need to save the start and finish times variables back to their original names. I also need to save the calculate duration times into the same naming convention. The naming convention is:

started_a started_b...
finished_a finished_b...
duration_a duration_b;

Can I do this?



data test (keep = started_time: finished_timeSmiley Happy;
set source;
run;

data test2;
set test;

array start_c $ started_time:; * picks up variables from previously defined list here done in the data step test. Great!;
array finish_c $ finished_time:;
array newstart stdate1-stdate100; ****need to sense array length of started_time:;
array newfin fndate1-fndate100;
array cdur calcdur1-calcdur100;

do i = 1 to dim(start_c);
newstart(i) = input(cat(scan(start_c(i), 3), scan(start_c(i), 2), scan(start_c(i), -1), '/', scan(start_c(i), 4)), datetime20.);
newfin(i) = input(cat(scan(finish_c(i), 3), scan(finish_c(i), 2), scan(finish_c(i), -1), '/', scan(finish_c(i), 4)), datetime20.);
cdur(i) = newfin(i) - newstart(i);
end;
format stdate: fndate: datetime20.;
format calcdur: time10.;
run;
Super Contributor
Posts: 359

Re: variable looping

Posted in reply to blackraven
Something like this would let you create arrays and renam strings dynamically. I have renamed your arrigional variables so that the dates (numeric) could be stored in the variables which were character before. (Note: I wrote this quickly so I cannot guarantee it is flawless)


data source;
length started_a started_b started_c finished_a finished_b finished_c $22;
infile datalines dsd dlm=',';
input varid $ started_a $ started_b $ started_c $ finished_a $ finished_b $ finished_c $ ;
return;
datalines;
id0001,"2008 mar 16 01:11:10","2008 mar 17 02:22:22","2008 mar 18 03:33:33","2008 mar 16 02:22:20","2008 mar 17 5:55:23","2008 mar 18 04:44:55"
id0002,"2008 jan 26 01:22:22","2008 jan 27 02:22:33","2008 jan 28 03:23:33","2008 jan 26 01:28:33","2008 jan 27 03:33:44","2008 jan 28 05:23:55"
;
run;

proc sql;
select trim(name) || ' = tmp'||trim(name) into :renamestr separated by ' '
from dictionary.columns where
libname = 'WORK' and memname = 'SOURCE' and ( name like 'start%' or name like 'finish%');
select 'tmp'||trim(name) into :strtstr separated by ' '
from dictionary.columns where
libname = 'WORK' and memname = 'SOURCE' and name like 'start%' ;
select 'tmp'||trim(name) into :finstr separated by ' '
from dictionary.columns where
libname = 'WORK' and memname = 'SOURCE' and name like 'finish%';
select trim(name) into :strtdtstr separated by ' '
from dictionary.columns where
libname = 'WORK' and memname = 'SOURCE' and name like 'start%' ;
select trim(name) into :findtstr separated by ' '
from dictionary.columns where
libname = 'WORK' and memname = 'SOURCE' and name like 'finish%';
select 'calcdur_' || scan(name, 2, '_') into :durstr separated by ' '
from dictionary.columns where
libname = 'WORK' and memname = 'SOURCE' and name like 'finish%';
quit;
%put &renamestr;

data test( drop = &strtstr &finstr);
set source(rename = (&renamestr));
array start $ &strtstr;
array finish $ &finstr;
array cdur &durstr;
array newstart &strtdtstr ;
array newfin &findtstr ;
*Your stuff ;
run;
Contributor
Posts: 36

Re: variable looping

Yes, finally!

I have been able to pull this off with a few modifications and I also put this into a macro, as I have several sets of time variables using different naming conventions. But I wouldn't dare to loop over that one!

Thanks!
Ask a Question
Discussion stats
  • 11 replies
  • 261 views
  • 0 likes
  • 3 in conversation