DATA Step, Macro, Functions and more

Concatenate if same month

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Concatenate if same month

I have a dataset with id, release_dare, and a a variable called datenum

Id $4
release_date mmddyy.
Datenum $4

There are several rows per id since each can have many release dates.

I want to create a one row per person with the following:

Id, month1-month24 that each contains the datenum variable for the corresponding month of the release from release_date. If there sre 2 or more on the same month, concatenate the datenum.

So if i have this as input:

Id, Release_date, Datenum
1, 03/02/11, 1245
2, 01/01/11, 3423
2, 01/10/11, 5432

At the end, i want this

Id, month1, month2, month 3......month24
1, , , 1245, .......
2, 34235432, ,.....

Accepted Solutions
Solution
‎03-29-2017 11:43 AM
PROC Star
Posts: 1,561

Re: Concatenate if same month

[ Edited ]

Like this?

data HAVE;
input ID RELEASE_DATE mmddyy8. DATENUM   ;
cards;
1 03/02/11 1245
2 01/01/11 3423
2 01/10/11 5432
run;
data WANT;
  set HAVE;
  by ID ;
  array MONTH[24] $32;
  retain MONTH1-MONTH24;
  INDEX=intck('month','01dec2010'd,RELEASE_DATE);
  MONTH[INDEX]=catt(MONTH[INDEX],DATENUM);
  if last.ID then do;
    output;
    call missing(of MONTH{*]);
  end;
run;
proc print noobs;
  var ID MONTH1-MONTH3;
run;

SAS Output

ID MONTH1 MONTH2 MONTH3
1     1245
2 34235432    

 

 

View solution in original post


All Replies
Solution
‎03-29-2017 11:43 AM
PROC Star
Posts: 1,561

Re: Concatenate if same month

[ Edited ]

Like this?

data HAVE;
input ID RELEASE_DATE mmddyy8. DATENUM   ;
cards;
1 03/02/11 1245
2 01/01/11 3423
2 01/10/11 5432
run;
data WANT;
  set HAVE;
  by ID ;
  array MONTH[24] $32;
  retain MONTH1-MONTH24;
  INDEX=intck('month','01dec2010'd,RELEASE_DATE);
  MONTH[INDEX]=catt(MONTH[INDEX],DATENUM);
  if last.ID then do;
    output;
    call missing(of MONTH{*]);
  end;
run;
proc print noobs;
  var ID MONTH1-MONTH3;
run;

SAS Output

ID MONTH1 MONTH2 MONTH3
1     1245
2 34235432    

 

 

Contributor
Posts: 37

Re: Concatenate if same month

thank you! that worked perfectly!!

Super User
Posts: 17,784

Re: Concatenate if same month

If you need it for a report use proc tabulate. 

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 101 views
  • 0 likes
  • 3 in conversation