BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
moreka
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Vince28_Statcan
Quartz | Level 8


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

View solution in original post

6 REPLIES 6
Astounding
PROC Star

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

BY

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.

moreka
Obsidian | Level 7

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!

Vince28_Statcan
Quartz | Level 8


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

moreka
Obsidian | Level 7

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!

Tom
Super User Tom
Super User

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?

moreka
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1025 views
  • 0 likes
  • 4 in conversation