convert month data to date data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

convert month data to date data

Hi, I have a question regarding dates in sas. I want to merge two datasets, one with a month variable formatted as "199010" and the other with a date variable formatted as "01OCT1990". I want to merge by the month. In this case "199010" and "01OCT1990" should be merged. How can I do that? I was thinking of several ways. One is that extract month and year of the two variables and merge by month and year. Another is to convert two variables to the dates variable and merge by one variable. But I am not sure how to convert "199010" to "01OCT1990" or "31OCT1990". Please help. Thanks.


Accepted Solutions
Solution
‎07-05-2013 04:33 PM
Super User
Super User
Posts: 7,076

Re: convert month data to date data

If your data are already date variables that just have different formats attached then use INTNX() function to convert to first day of the month.  If they are character strings then use INPUT function.  If the first is a number like 199,010 that just happens to look like 6 digit YYYYMM string then convert it to string with the PUT function first.

data check ;

  input s1 :$6. s2 :$9. @1 n1 ;

  d1 = input(s1,yymmn6.);

  d2 = input(s2,date9.);

  d2m = intnx('month',d2,0);

  d3 = input(put(n1,z6.),yymmn6.);

  format d: date9. ;

  put (_all_) (=);

cards;

199010 31OCT1990

run;


s1=199010 s2=31OCT1990 n1=199010 d1=01OCT1990 d2=31OCT1990 d2m=01OCT1990 d3=01OCT1990

View solution in original post


All Replies
Solution
‎07-05-2013 04:33 PM
Super User
Super User
Posts: 7,076

Re: convert month data to date data

If your data are already date variables that just have different formats attached then use INTNX() function to convert to first day of the month.  If they are character strings then use INPUT function.  If the first is a number like 199,010 that just happens to look like 6 digit YYYYMM string then convert it to string with the PUT function first.

data check ;

  input s1 :$6. s2 :$9. @1 n1 ;

  d1 = input(s1,yymmn6.);

  d2 = input(s2,date9.);

  d2m = intnx('month',d2,0);

  d3 = input(put(n1,z6.),yymmn6.);

  format d: date9. ;

  put (_all_) (=);

cards;

199010 31OCT1990

run;


s1=199010 s2=31OCT1990 n1=199010 d1=01OCT1990 d2=31OCT1990 d2m=01OCT1990 d3=01OCT1990

SAS Employee
Posts: 416

Re: convert month data to date data

Hi -

Assuming that your variables are recorded as character strings, your best bet is to create a SAS date variable and merge the 2 data sets by using this new variable.

As an illustration:

data have1;

month="199010";

value1=1;

date=input(month,yymmn6.);

format date date9.;

run;

data have2;

day="01OCT1990";

value2=2;

date=input(day,date9.);

format date date9.;

run;

data want;

merge have1 have2;

by date;

run;

For more information about SAS date variables, check out: http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#n0q9ylcaccjgjrn19hvq...

Thanks,

Udo

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 5715 views
  • 6 likes
  • 3 in conversation