Hello SAS users.
I have a dataset looks like below.
crsp_fundno | begdt | enddt | crsp_obj_cd |
1 | 19971231 | 19981230 | ICQM |
1 | 19981231 | 19991230 | ICQY |
1 | 19991231 | 20000731 | ICQY |
2 | 19971231 | 19981230 | EDYG |
2 | 19981231 | 19991230 | EDYB |
2 | 19991231 | 20000831 | EDYB |
What I want to do is to change this dataset into
crsp_fundno | date (month end) | crsp_obj_cd |
1 | 19971231 | ICQM |
1 | 19980131 | ICQM |
1 | 19980228 | ICQM |
1 | 19980331 | ICQM |
1 | 19980430 | ICQM |
1 | 19980531 | ICQM |
1 | 19980630 | ICQM |
1 | 19980731 | ICQM |
1 | 19980830 | ICQM |
1 | 19980931 | ICQM |
1 | 19981030 | ICQM |
1 | 19981131 | ICQM |
1 | 19981231 | ICQY |
1 | 19990131 | ICQY |
1 | 19990228 | ICQY |
… | ||
… | ||
… | ||
2 | 19971231 | EDYG |
2 | 19980131 | EDYG |
2 | 19980228 | EDYG |
2 | 19980331 | EDYG |
2 | 19980430 | EDYG |
2 | 19980531 | EDYG |
2 | 19980630 | EDYG |
2 | 19980731 | EDYG |
2 | 19980830 | EDYG |
2 | 19980931 | EDYG |
2 | 19981030 | EDYG |
2 | 19981131 | EDYG |
… |
in this situation how do I manage my dataset??
data ex;
input crsp_fundno begdt enddt crsp_obj_cd $;
cards;
1 19971231 19981230 ICQM
1 19981231 19991230 ICQY
1 19991231 20000731 ICQY
2 19971231 19981230 EDYG
2 19981231 19991230 EDYB
2 19991231 20000831 EDYB
;
run;
1. Import your dates as dates, not numbers
2. use INTCK to find the number of intervals between start and end date
3. Use a DO LOOP with INTNX and OUTPUT to output the number of records desired.
Untested
data want;
set have;
num_months = intck('month', start_date, end_date);
do i=1 to num_months;
date = intnx('month', start_date, i, 'e');
OUTPUT;
end;
RUN:
@jkim197 wrote:
Hello SAS users.
I have a dataset looks like below.
crsp_fundno begdt enddt crsp_obj_cd 1 19971231 19981230 ICQM 1 19981231 19991230 ICQY 1 19991231 20000731 ICQY 2 19971231 19981230 EDYG 2 19981231 19991230 EDYB 2 19991231 20000831 EDYB
What I want to do is to change this dataset into
crsp_fundno date (month end) crsp_obj_cd 1 19971231 ICQM 1 19980131 ICQM 1 19980228 ICQM 1 19980331 ICQM 1 19980430 ICQM 1 19980531 ICQM 1 19980630 ICQM 1 19980731 ICQM 1 19980830 ICQM 1 19980931 ICQM 1 19981030 ICQM 1 19981131 ICQM 1 19981231 ICQY 1 19990131 ICQY 1 19990228 ICQY … … … 2 19971231 EDYG 2 19980131 EDYG 2 19980228 EDYG 2 19980331 EDYG 2 19980430 EDYG 2 19980531 EDYG 2 19980630 EDYG 2 19980731 EDYG 2 19980830 EDYG 2 19980931 EDYG 2 19981030 EDYG 2 19981131 EDYG …
in this situation how do I manage my dataset??
data ex; input crsp_fundno begdt enddt crsp_obj_cd $; cards; 1 19971231 19981230 ICQM 1 19981231 19991230 ICQY 1 19991231 20000731 ICQY 2 19971231 19981230 EDYG 2 19981231 19991230 EDYB 2 19991231 20000831 EDYB ; run;
1. Import your dates as dates, not numbers
2. use INTCK to find the number of intervals between start and end date
3. Use a DO LOOP with INTNX and OUTPUT to output the number of records desired.
Untested
data want;
set have;
num_months = intck('month', start_date, end_date);
do i=1 to num_months;
date = intnx('month', start_date, i, 'e');
OUTPUT;
end;
RUN:
@jkim197 wrote:
Hello SAS users.
I have a dataset looks like below.
crsp_fundno begdt enddt crsp_obj_cd 1 19971231 19981230 ICQM 1 19981231 19991230 ICQY 1 19991231 20000731 ICQY 2 19971231 19981230 EDYG 2 19981231 19991230 EDYB 2 19991231 20000831 EDYB
What I want to do is to change this dataset into
crsp_fundno date (month end) crsp_obj_cd 1 19971231 ICQM 1 19980131 ICQM 1 19980228 ICQM 1 19980331 ICQM 1 19980430 ICQM 1 19980531 ICQM 1 19980630 ICQM 1 19980731 ICQM 1 19980830 ICQM 1 19980931 ICQM 1 19981030 ICQM 1 19981131 ICQM 1 19981231 ICQY 1 19990131 ICQY 1 19990228 ICQY … … … 2 19971231 EDYG 2 19980131 EDYG 2 19980228 EDYG 2 19980331 EDYG 2 19980430 EDYG 2 19980531 EDYG 2 19980630 EDYG 2 19980731 EDYG 2 19980830 EDYG 2 19980931 EDYG 2 19981030 EDYG 2 19981131 EDYG …
in this situation how do I manage my dataset??
data ex; input crsp_fundno begdt enddt crsp_obj_cd $; cards; 1 19971231 19981230 ICQM 1 19981231 19991230 ICQY 1 19991231 20000731 ICQY 2 19971231 19981230 EDYG 2 19981231 19991230 EDYB 2 19991231 20000831 EDYB ; run;
Thank You Reeza it works really well !!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.