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

 

Dear SAS Community:

 

Dataset T created by submitting following SAS code.

 

data T;
acnum=5041;
min_ac_dt='01Jan2000'd;
max_ac_dt='01Jun2010'd;
format min_ac_dt max_ac_dt DATE9.;
run;

 

I need to create additional column "Range_Values" as shown in the attached "sample_output.png" file.below.

 

 

 

I am trying with this macro...no luck yet:

 

%macro date_loop(start,end);
%let start=%sysfunc(inputn(&start,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.));

%let dif=%sysfunc(intck(month,&start,&end));
%do i=0 %to &dif;
%let date=%sysfunc(putn(%sysfunc(intnx(month,&start,&i,b)),yymmp10.));
%put &date;
%end;
%mend;
%date_loop(01jan2000,01jun2010);

 

 


sample_ouput.PNG
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Get off macros when you don't need them. Simple datastep programming will get you what you want:

 

data T;
acnum=5041;
min_ac_d='01Jan2000'd;
max_ac_d='01Jun2010'd;
format min_ac_d max_ac_d DATE9.;
length range_values $2000;
month_d = intnx("MONTH", max_ac_d, 0);
do while (month_d >= min_ac_d);
    range_values = catx(";", range_values, put(month_d, yymmp10.));
    month_d = intnx("MONTH", month_d, -1);
    end;
drop month_d;
run;
PG

View solution in original post

6 REPLIES 6
Reeza
Super User

Why macros? Use a loop within a data step and/or a CATX() function. 

 

I would suggest you try this first and post back if you have issues and we can help with debugging. You're pretty close but I think the macro part is making this overly difficult. 

 

You also want to look into the INTNX function which can increment your dates by one month. 

 

GPatel
Pyrite | Level 9
I tried intnx function.....

##- Please type your reply above this line. Simple formatting, no
attachments. -##
PGStats
Opal | Level 21

Get off macros when you don't need them. Simple datastep programming will get you what you want:

 

data T;
acnum=5041;
min_ac_d='01Jan2000'd;
max_ac_d='01Jun2010'd;
format min_ac_d max_ac_d DATE9.;
length range_values $2000;
month_d = intnx("MONTH", max_ac_d, 0);
do while (month_d >= min_ac_d);
    range_values = catx(";", range_values, put(month_d, yymmp10.));
    month_d = intnx("MONTH", month_d, -1);
    end;
drop month_d;
run;
PG
GPatel
Pyrite | Level 9
Thank you! It is perfect solution.

Ksharp
Super User

data T;
acnum=5041;
min_ac_d='01Jan2000'd;
max_ac_d='01Jun2010'd;
format min_ac_d max_ac_d DATE9.;
length value $ 20;
do i=min_ac_d  to max_ac_d;
 month=month(i);
 if lag_month ne month then do;
  year=year(i);value=put(i,yymmp.);output;lag_month=month;
 end;
end;
drop i month lag_month;
run;

data want;
length range $ 200;
do until(last.year);
 set t;
 by acnum year;
 range=catx(';',value,range);
end;
drop value year;
run;
GPatel
Pyrite | Level 9
Thanks, it is also elegant solution.
I am proud of smart SAS ......


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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 1212 views
  • 1 like
  • 4 in conversation