topic Re: Macro to add two variables and 90 rows to a table in SAS Programming

Macro to add two variables and 90 rows to a table

sharda — Wed, 19 Oct 2016 17:47:59 GMT

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;

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

Reeza — Wed, 19 Oct 2016 18:14:26 GMT

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.

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

sharda — Wed, 19 Oct 2016 18:20:40 GMT

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/2016	200
10/2/2016	200
10/3/2016	200

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/2016	200
10/2/2016	200
10/3/2016	200
10/19/2016	201
10/20/2016	201
10/21/2016	201
etc (until the 90 day from 10/19/16)	201

Thanks,
Sharda

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

Shmuel — Wed, 19 Oct 2016 19:24:54 GMT

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).

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

ballardw — Wed, 19 Oct 2016 19:42:57 GMT

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.

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

sharda — Wed, 19 Oct 2016 20:32:43 GMT

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.

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

ballardw — Wed, 19 Oct 2016 23:27:58 GMT

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;

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

sharda — Thu, 20 Oct 2016 14:48:48 GMT

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;

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

Shmuel — Thu, 20 Oct 2016 14:56:28 GMT

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);

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

sharda — Thu, 20 Oct 2016 15:12:03 GMT

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;

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

sharda — Thu, 20 Oct 2016 15:26:42 GMT

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;

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

Shmuel — Thu, 20 Oct 2016 15:56:48 GMT

You probably wanted to code:

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