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

Hello,

 

I am new to SAS programming, I have created a macro function which contains a DO loop and an IF THEN  statement, I am looking to save a calculated value between iterations to be used in the next loop, I am wondering if you may know a method to perform this operation. Only the first iteration of my code produces a value, subsquent iterations do not produse values, and when I print the macro save with %put &save the result is diff. I have bolded the code that is providing me with issues.

The input format of  Notif_date and InstallDate is ddmmyyyy , where as the output of diff is in days.

 

Thank you for the help in advance.

 

Please see my code below:

 

%macro DiffCalc();
%do id = 1 %to 1 /*&count*/;
      proc sort data= work.temp&id;
             by Notif_Date;
       run;
 
      proc sql;
            select count(unit) into : num from work.temp&id;
      run;
      quit;
 
      data work.temp&id;
            set work.temp&id;
            by Note;
            retain H;
            if first.Note then H= sum(H,1);
      run;
 
do H=1 to #
      data work.temp&id;
            set work.temp&id;
                  if H=1 then do;
                        diff= Notif_date-InstallDate;
                        %let save = diff;
                        end;
                  else do;
                        diff = (Notif_date-InstallDate)- &save;
                        %let save = diff;
                  end;
                run;
         end;
       
  %end;
  %mend;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
NK15
Fluorite | Level 6

Thank you for the response Tom,

2) I created and attempted to use the count created within the secondary DO loop and did not delete it (sorry)

3) I had to perform the count in terms of Note, as some of the Notif_Date are the same, and for some reason the code would provide all of those values with the same date the same H count value. 

 

The code you provided below only runs within the first loop of the IF statement,  it does not make it into the ELSE, as the output H values never increase past 1, and all calculated values correspond with only the calculation completed in the IF.

 

But, yes, that is similar to what I am trying to achieve, 

For the first entry, find the days between the installation and the first note.

Then find the time between the first note, and the second note, then between second and third note etc... For the rest of the data.

 

When I modified my code the following worked to provide the required output, adding the H count appeared to force the continuation into the ELSE statement.

 

%macro DiffCalc();
%do id = 1 %to &count;
      proc sort data= work.temp&id;
             by Notif_Date;
       run;
 
       
      data work.temp&id;
            set work.temp&id;
            by Note;
            retain H;
            if first.Note then H= sum(H,1);
      run;
 

      data work.temp&id;
            set work.temp&id;
                 by Note;
                  retain Diff
                  if H=1 then do;
                        Diff= Notif_date-InstallDate;
                       end;
                  else do;
                        diff2 = (Notif_date-InstallDate)- Diff;
                        Diff = diff2;
                  end;
                run;
         
       
  %end;
  %mend;

 

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Am a bit confused by your post, why are you creating x number of datasets?  Anyways, to get good answers, post test data in the form of a datastep so that we can see some data and the structure (post it in a code block which is the {i} above the post), then post what the output should look like.  I don't see anything from the code which would indicate any need for loops/macro/mutiple datasets?

NK15
Fluorite | Level 6

Thank you for the response,

 

This code is approx 300 lines into the overall SAS code. From the main dataset that I imported into the program I have modified it any ways as I have approx. 50 datasets, where each set contains various number of rows ( from 1 to 60) , however each data set contains the same columns. For demonstration I have limited the looping capabilities to a single dataset.

 

The main thing I am looking at is just the bolded do loop, as the code before that works as required, my issue is that after setting the macro %save, it only saves diff, as the text diff. I am wondering if there is a method to either save, or modify the output to the %save macro to have it in a format readily available to preform calculations.

Tom
Super User Tom
Super User

Macro code is used to generate SAS code.  If you want to make a macro first you need to figure out what SAS code you need to run. Then you can see what parts need to be dynamically generated and replace them with macro variable references.  Then you can add the macro logic to set the values of the macro variables and use those to generate the SAS code.

 

In your program you have a number of mixed up things. One you have a datastep DO loop outside of a data step. If you want to take the value of a dataset variable and assign it to a macro variable you need to use CALL SYMPUTX() function. Using something like:

 

%let save=diff;

Is just going to set save to the letters 'diff'.  That might be useful if DIFF was the name of a variable and the way that you wanted to make you SAS code generation dynamic was by changing the name of the variable it uses. 

 

Can you explain what problem you are trying to solve and then perhaps someone can help you create a program to do that. Note that it might not need to use macro logic or even macro variables depending on what your actual problem is.

NK15
Fluorite | Level 6

Thank you for the response,

The main goal of the calculation is to determine the time from the initial installation date, to the first notification. Then to determine the subsequent time between notifications. 

As such I am tring to determine a method that will allow me to save a value and then reuse it between iterations. I'm not set on using macros, I am just tring to find a method that would allow me to save a value and reuse/ save it. 

 

My initial code did not contain the secondary do loop and it provided me with the same output 

%macro DiffCalc();
%do id = 1 %to 1 /*&count*/;
      proc sort data= work.temp&id;
             by Notif_Date;
       run;
 
      proc sql;
            select count(unit) into : num from work.temp&id;
      run;
      quit;
 
      data work.temp&id;
            set work.temp&id;
            by Note;
            retain H;
            if first.Note then H= sum(H,1);
      run;
 

      data work.temp&id;
            set work.temp&id;
                  if H=1 then do;
                        diff= Notif_date-InstallDate;
                        %let save = diff;
                        end;
                  else do;
                        diff = (Notif_date-InstallDate)- &save;
                        %let save = diff;
                  end;
                run;
         
       
  %end;
  %mend;
Tom
Super User Tom
Super User

What are you trying to do?  Right now your code is doing these steps.

1) Sorting by NOTIF_DATE.

2) Counting how many observations have a non-missing value of UNIT.  This number is never used for anything.

3) Trying to add an observation counter called H that counts obervations within value of NOTE, but that step will fail since you just sorted the data by NOTIF_DATE instead of by NOTE.  Did you mean to sort by NOTE NOTIF_DATE in the first step?

4) Seems to be calculating the difference in days between the dates?  Or perhaps it is trying to calculate the difference in days from the first date?

Perhaps you want to do something like this?

%macro DiffCalc(count);
%do id = 1 %to &count ;
  proc sort data= work.temp&id;
    by Note Notif_Date;
  run;
  data work.temp&id;
    set work.temp&id;
    by Note;
    retain H DIFF ;
    if first.note then do;
      H=1;
      diff = notif_date - installdate;
    end;
    else do;
      H+1;
      diff = (notif_date-installdate) - diff;
    end;
  run;
%end;
%mend diffcalc;
%diffcalc(1);

 

 

NK15
Fluorite | Level 6

Thank you for the response Tom,

2) I created and attempted to use the count created within the secondary DO loop and did not delete it (sorry)

3) I had to perform the count in terms of Note, as some of the Notif_Date are the same, and for some reason the code would provide all of those values with the same date the same H count value. 

 

The code you provided below only runs within the first loop of the IF statement,  it does not make it into the ELSE, as the output H values never increase past 1, and all calculated values correspond with only the calculation completed in the IF.

 

But, yes, that is similar to what I am trying to achieve, 

For the first entry, find the days between the installation and the first note.

Then find the time between the first note, and the second note, then between second and third note etc... For the rest of the data.

 

When I modified my code the following worked to provide the required output, adding the H count appeared to force the continuation into the ELSE statement.

 

%macro DiffCalc();
%do id = 1 %to &count;
      proc sort data= work.temp&id;
             by Notif_Date;
       run;
 
       
      data work.temp&id;
            set work.temp&id;
            by Note;
            retain H;
            if first.Note then H= sum(H,1);
      run;
 

      data work.temp&id;
            set work.temp&id;
                 by Note;
                  retain Diff
                  if H=1 then do;
                        Diff= Notif_date-InstallDate;
                       end;
                  else do;
                        diff2 = (Notif_date-InstallDate)- Diff;
                        Diff = diff2;
                  end;
                run;
         
       
  %end;
  %mend;

 

Tom
Super User Tom
Super User

Your program still has a logic problem. You are sorting by one variable and then process the data as if it was sorted by a different variable.  That might work for your current data set if there is some relationship between the two variables.

 

If you just want to calculate the difference between the values you can use the DIF() function, but be careful not to execute it conditionally.  Instead conditionally reset the value when it doesn't make sense to use. For example when you are at the first observation for a group.  Here is simple example:

 

data have ;
  input group_var $ date yymmdd10. ;
  format date yymmdd10.;
cards;
A 2017-06-15 
A 2017-06-30
A 2017-07-04
B 2015-01-01
B 2015-01-02
;

data want ;
  set have ;
  by group_var ;
  row_number + 1;
  days_since = dif(date);
  if first.group_var then do;
    row_number=1;
    days_since=.;
  end;
run;

Capture.PNG

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
  • 7 replies
  • 5474 views
  • 0 likes
  • 3 in conversation