topic Re: Count Variable inside Macro in SAS Programming

Count Variable inside Macro

moreka — Sat, 07 Sep 2013 18:52:30 GMT

I would like to create a variable that increases by 1 only when another variable satisfies some criterion.

For example, in drug1, the stopdates are at the 2rd, 4th, and 6th visits; whereas with drug2, the stopdates are at the 3rd and 5th visits, and the stopdate counts from 1-2 with a missing at 3 since there is no 3rd time stopping the drug.

The data should come out where:

visitdate_1_2 = stopdate1_1

visitdate_1_4 = stopdate1_2

visitdate_1_6 = stopdate1_3

visitdate_2_3 = stopdate2_1

visitdate_2_5 = stopdate2_2

. = stopdate2_3

The macro below is what I'm working with. The [??????] is where I'm guessing a macro variable or function might go.

%macro duration (n,m);

data two; set one;

%do i=1 %to &n.; *drug number, up to 10;

%do j=1 %to &m.; *changes to each drug, up to 6;

if changed&i._&j.='stop' then do stopdate&i._[???????]= (visitdate_&i._&j.);

%end; %end;

run;

%mend;

I attempted another do loop inside the other two, but it just wrote one date on top of the last, so they were all the same.

Any suggestions? Thanks!

Re: Count Variable inside Macro

Astounding — Sat, 07 Sep 2013 19:37:22 GMT

I could be wrong, but it looks like what you're trying to do has absolutely nothing to do with macros. I would suggest studying the following DATA step statements (what they do, when they can be used):

RETAIN

OUTPUT

It's possible that arrays might come into the picture as well.

As a general rule, macro language won't help you unless you can picture what the final program should look like after the macro language has done its work.

Good luck.

Re: Count Variable inside Macro

moreka — Sun, 08 Sep 2013 20:08:46 GMT

I think you may be right that I can't solve this with a macro and an array may need to be used. Though, since the dataset is in wide-form (like what's reflected in the macro above), I'm not sure if I would be able to sort on a specific variable or create a dataset based on a specific outcome, since the outcome of interest can occur multiple times and in any one of 60 variables per observation (6 drugs*10 changes). Unless of course, I'm misunderstanding, which is entirely possible! I've used similar macros to the one above on this dataset and have had a lot of success pulling out information, but this last problem has me stumped. Thanks for your input!

Re: Count Variable inside Macro

Vince28_Statcan — Mon, 09 Sep 2013 11:59:24 GMT

It would be possible in macros if you used fetch and the like to read your data steps through macros instead of via a data step but it would be much more of a hassle.

However, you can thinker with the way your data is setup and indirectly increment with additionnal conditioning on your data. Since all of your data is within a single row you could do something like this:

%macro duration (n,m);

data two; set one;

%do i=1 %to &n.; *drug number, up to 10;

%do j=1 %to &m.; *changes to each drug, up to 6;

if changed&i._&j.='stop' and stopdate&i._1=. then do stopdate&i._1= (visitdate_&i._&j.);

%do k=2 %to 3;

else if changed&i._&j.='stop' and stopdate&i._&k.=. then do stopdate&i._&k.= (visitdate_&i._&j.);

%end;

%end; %end;

run;

%mend;

I've assumed, since you can only have 6 changed to drugs, that the maximum number of times one could change drug would be of 3 hence the hard typed but for so long as you know a priori how many stopdate is the maximum you can get, you can change the upper bound of the loop on k.

Vincent

Re: Count Variable inside Macro

Tom — Mon, 09 Sep 2013 13:55:31 GMT

data want ;

set have ;

array sd1 stopdate1_1 - stopdate1_3 ;

array sd2 stopdate2_1 - stopdate2_3 ;

array vd1 visitdate_1_1 - visitdate_1_6 ;

array vd2 visitdate_2_1 - visitdate_2_5 ;

sd1(1) = vd1(2);

sd1(2) = vd1(4);

sd1(3) = vd1(6);

sd2(1) = vd2(3);

sd2(2) = vd2(5);

sd2(3) = . ;

run;

Not sure where macro logic comes into this problem. Are you trying to find a generalized solution?

If so what is it that varies?

Re: Count Variable inside Macro

moreka — Mon, 09 Sep 2013 15:44:47 GMT

Thanks Vincent, this is the sort of solution I was aiming for, and once I close the do loop, it works perfectly inside the macro!

Re: Count Variable inside Macro

moreka — Mon, 09 Sep 2013 16:00:15 GMT

Tom, I don't think I introduced this very clearly.

My data is in wide-form, with each drug, the changes for each drug and the dates for each change, all in one row.

The macro I included is what I've frequently used to process other information for this dataset, so I was looking for something that would work in this context.

Since any of the 60 date variables could be a potential stop date, I wanted to only pull out the visit dates on which the drugs were stopped and have those stop dates count sequentially.

Given that, it looks like the indirect incrementing inside the macro works to output specific dates I want to capture.

Thanks and sorry that wasn't more straightforward from the get-go!