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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

2 REPLIES 2
Tom
Super User Tom
Super User

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

udo_sas
SAS Employee

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

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
  • 12317 views
  • 6 likes
  • 3 in conversation