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

Hi SAS Community

 

I am rather new to SAS.

I want to built a monthly panel data set from the following data structure:

IdentifierStart dateEnd date
X01/01/198531/03/1985
Y01/04/199930/04/2005

 

And the data set i want looks like this:

Identifier:Month
XJanuary 1985
XFebruary 1985
XMarch 1985
YApril 1999
YMay 1999
Y...
YApril 2005

I have tried some different approaches. However, none of them have seemed to work.

For example I have used the intnx-command to create a new variable with the next month prior to the startdate. But going from a new variable to a new observation have troubled me, so I hope you can help me.

 

Best regards Rasmus

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

How about this?

 

data have;
input Identifier $ (StartDate EndDate)(:ddmmyy10.);
format StartDate EndDate ddmmyy10.;
datalines;
X 01/01/1985 31/03/1985
Y 01/04/1999 30/04/2005
;

proc format; 
  picture dtfmt (default=30)
    other = '%B %Y' (datatype=date)
  ;
run;

data want;
   set have;
   Month = StartDate;
   output;
   do while (Month < EndDate);
      Month = intnx ('month', Month, 1, 'e');
      output;
   end;
   format month dtfmt.;
run;

View solution in original post

2 REPLIES 2
PeterClemmensen
Tourmaline | Level 20

How about this?

 

data have;
input Identifier $ (StartDate EndDate)(:ddmmyy10.);
format StartDate EndDate ddmmyy10.;
datalines;
X 01/01/1985 31/03/1985
Y 01/04/1999 30/04/2005
;

proc format; 
  picture dtfmt (default=30)
    other = '%B %Y' (datatype=date)
  ;
run;

data want;
   set have;
   Month = StartDate;
   output;
   do while (Month < EndDate);
      Month = intnx ('month', Month, 1, 'e');
      output;
   end;
   format month dtfmt.;
run;
yabwon
Onyx | Level 15

Hi,

 

something like this:

data have;
input Identifier $	Start_date : ddmmyy10.	End_date : ddmmyy10.;
format Start_date ddmmyy10.	End_date ddmmyy10.;
cards4;
X 01/01/1985 31/03/1985
Y 01/04/1999 30/04/2000
;;;;
run;

data want;
  set have;

  do _N_=0 by 1 until(_Month_ > End_date);
    _Month_ = intnx("month", start_date, _N_, "B");
    length Month $ 50; /* Month is se tup as text */
    Month = catx(" ", put(_Month_, Monname.), year(_Month_));
    output;
  end;
  keep Identifier Month;
run;
proc print;
run;

 

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1216 views
  • 3 likes
  • 3 in conversation