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

Hello SAS users.

I have a dataset looks like below.

 

crsp_fundnobegdtenddtcrsp_obj_cd
11997123119981230ICQM
11998123119991230ICQY
11999123120000731ICQY
21997123119981230EDYG
21998123119991230EDYB
21999123120000831EDYB

 

 

 

What I want to do is to change this dataset into

 

crsp_fundnodate (month end)crsp_obj_cd
119971231ICQM
119980131ICQM
119980228ICQM
119980331ICQM
119980430ICQM
119980531ICQM
119980630ICQM
119980731ICQM
119980830ICQM
119980931ICQM
119981030ICQM
119981131ICQM
119981231ICQY
119990131ICQY
119990228ICQY
  
   
   
219971231EDYG
219980131EDYG
219980228EDYG
219980331EDYG
219980430EDYG
219980531EDYG
219980630EDYG
219980731EDYG
219980830EDYG
219980931EDYG
219981030EDYG
219981131EDYG
  

 

 

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 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

 


 

View solution in original post

2 REPLIES 2
Reeza
Super User

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;

 


 

jkim197
Obsidian | Level 7

 

Thank You Reeza it works really well !!

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
  • 2 replies
  • 824 views
  • 3 likes
  • 2 in conversation