DATA Step, Macro, Functions and more

Macro to add two variables and 90 rows to a table

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Macro to add two variables and 90 rows to a table

Hello,

 

What am I missing in this macro to add two values for 90 records?

 

libname pymdata '/sas/shared/PYMDATA';

%macro multidates;
proc sql;
insert into pymdata.timeframe_master
values ("&datecnt"d, "&tfcnt");
%let datecnt = &sqlobs;
%let tfcnt = &sqlobs
quit;

%do i = 1 %to "&datecnt"d;
%do i = 1 %to "&tfcnt";

proc sql;
delete from pymdata.timeframe_master
where date is null;
quit;

run;
%END;
%mend multidates;
%multidates;

 

 


Accepted Solutions
Solution
‎10-25-2016 09:14 AM
Contributor
Posts: 40

Re: Macro to add two variables and 90 rows to a table

Hello All,


Thank you very much for your ideas and solutions, I think I got it now...see &x below.

 

 

libname pymdata '/sas/shared/PYMDATA';

data pymdata.timeframe_master;

set pymdata.timeframe_master end=last;

retain lasttimeframe;

output;

lasttimeframe = timeframe;

if last then do date = today() to (today()+ &x);

timeframe = lasttimeframe+1;

output;

end;

drop lasttimeframe;

run;

proc sql;

delete from pymdata.timeframe_master

where date is null;

quit;

View solution in original post


All Replies
Super User
Posts: 19,780

Re: Macro to add two variables and 90 rows to a table

You don't need a macro and it's probably complicating your code. Describe your requirements in plain language with a small sample input and output dataset. 

Contributor
Posts: 40

Re: Macro to add two variables and 90 rows to a table

Hi Reeza,

 

I wanted to create a stored process using code to add two variables with 90 rows to an existing table.

 

existing table: 

date tf
10/1/2016200
10/2/2016200
10/3/2016200

 

The stored process needs to add a date range (90 days from today) and timeframe value (same value) for 90 records so the table looks like:

date tf
10/1/2016200
10/2/2016200
10/3/2016200
10/19/2016201
10/20/2016201
10/21/2016201
etc (until the 90 day from 10/19/16)201

 

 

Thanks,
Sharda

Trusted Advisor
Posts: 1,556

Re: Macro to add two variables and 90 rows to a table

Do you want to have output with subset of 90 observations only ?

OR do you mean to update just part of your dataset starting 10/19/2016 counting 90 observations

OR update/subset those observations with DATE between '19OCT2016'd and intnx('DAY',  '19OCT2016'd, 90)  ?

 

Explain yourself and give an example of desired output (just few rows).

Super User
Posts: 11,343

Re: Macro to add two variables and 90 rows to a table

Where does the value 201 for TF come from? Is that just adding a value of 1 to the last existing date?

What would the rule be that the additional data starts at 10/19? Is it really the date the process runs? If so, what happens when the process gets run in 30 days? That would potentiall create an overlap of dates with different values for the TF variable.

 

It may also be appropriate to confirm that TF is indeed numeric and not a character variable.

Contributor
Posts: 40

Re: Macro to add two variables and 90 rows to a table

Yes that number increases by 1 from the last value for TF for a specific date range (it could be anywhere from 30-90 days form the last date). There is no overlap of days…the dates are consecutive for that value until the new date range changes the TF value.

 

TF is needing to be a character.

Super User
Posts: 11,343

Re: Macro to add two variables and 90 rows to a table

The overlap I mention is from running the process on the data set. Suppose I run it today and add 90 days then we have Oct 19 2016 to Jan 19 2017 (or there abouts). If the resulting data set of the process is then subjected to the same process in November then dates starting in November will be added at the end. If you never send the resulting set back through the process then no problem but many operations involving dates and time seem to be repeated by many organizations.

 

This seems to do what you are requesting:

data have;
   informat date mmddyy10. tf best5.;
   format date mmddyy10.;
   input date  tf  ;
datalines;
10/1/2016 200 
10/2/2016 200 
10/3/2016 200 
;
run;

data want;
   set have end=last;
   retain lasttf;
   output;
   lasttf = tf;
   if last then do date= today() to (today()+90);
      tf = lasttf+1;
      output;
   end;
   drop lasttf;
run;

Contributor
Posts: 40

Re: Macro to add two variables and 90 rows to a table

Thanks so much, this code is very close...I  wanted to make adjustments to include a macro for the date since the business needs the maximum to be 90 days but it could also be 30 or 60 days or less. Can you see if I have it correct below?

 

libname pymdata '/sas/shared/PYMDATA';

%let date = "&date"d;

data pymdata.timeframe_master;

set pymdata.timeframe_master end=last;

retain lasttimeframe;

output;

lasttimeframe = timeframe;

if last then do date = today() to (today()+ 1);

timeframe = lasttimeframe+1;

output;

end;

drop lasttimeframe;

run;

proc sql;

delete from pymdata.timeframe_master

where date is null;

quit;

 

 

Trusted Advisor
Posts: 1,556

Re: Macro to add two variables and 90 rows to a table

Two points to relate:

 

1) I don't see assigning a value to macro varaible DATE.

    on your 2nd line is:  %LET date = "&date"d.
    Is there in the log a message like: macro variable DATE not resolved ?

 

    For a date of today you should code:

    %LET DATE = "20OCT2016"d ;

 

2) You have misstyped on line:

    if last then do date = today() to (today()+ 90);

Contributor
Posts: 40

Re: Macro to add two variables and 90 rows to a table

Thanks but the 90 could change to any number so I used x to represent that number but the below code but it kicked back an error...any thoughts?

 

libname pymdata '/sas/shared/PYMDATA';

%let x = &x;

data pymdata.timeframe_master;

set pymdata.timeframe_master end=last;

retain lasttimeframe;

output;

lasttimeframe = timeframe;

if last then do date = today() to (today()+ x);

timeframe = lasttimeframe+1;

output;

end;

drop lasttimeframe;

run;

proc sql;

delete from pymdata.timeframe_master

where date is null;

quit;

Solution
‎10-25-2016 09:14 AM
Contributor
Posts: 40

Re: Macro to add two variables and 90 rows to a table

Hello All,


Thank you very much for your ideas and solutions, I think I got it now...see &x below.

 

 

libname pymdata '/sas/shared/PYMDATA';

data pymdata.timeframe_master;

set pymdata.timeframe_master end=last;

retain lasttimeframe;

output;

lasttimeframe = timeframe;

if last then do date = today() to (today()+ &x);

timeframe = lasttimeframe+1;

output;

end;

drop lasttimeframe;

run;

proc sql;

delete from pymdata.timeframe_master

where date is null;

quit;

Trusted Advisor
Posts: 1,556

Re: Macro to add two variables and 90 rows to a table

You probably wanted to code:

 

if last then do date = today() to (today()+ &x);  /* & added */

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 411 views
  • 0 likes
  • 4 in conversation