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

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
sharda
Obsidian | Level 7

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

11 REPLIES 11
Reeza
Super User

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. 

sharda
Obsidian | Level 7

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

Shmuel
Garnet | Level 18

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

ballardw
Super User

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.

sharda
Obsidian | Level 7

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.

ballardw
Super User

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;

sharda
Obsidian | Level 7

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;

 

 

Shmuel
Garnet | Level 18

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

sharda
Obsidian | Level 7

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;

sharda
Obsidian | Level 7

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;

Shmuel
Garnet | Level 18

You probably wanted to code:

 

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

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
  • 11 replies
  • 1252 views
  • 0 likes
  • 4 in conversation