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

Hello, everyone, I would like to properly import a CSV file which has the date in the following format

 

"ID","GENDER","BIRTH YEAR AND MONTH"
"M0058","Female","196106"
"M00123","Male","195108"
"M00331","Male","196002"
"M00366","Female","195001"
"M00440","Female","195607"
"M00458","Female","196501"
"M00584","Female","197902"
"M00611","Male","196601"
"M00673","Female","194306"

Once I import this file, I would like to calculate the difference between the dates in this format and another date which looks like 2020-11-18 (YYMMDD10. format)

 

Example: (2020-11-18) - (196106)

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Nothing's added:

data My_data;
infile datalines dlm="," dsd firstobs=2 truncover;
input id :$11. gender :$8. birth_year_and_month :yymmn.;
format birth_year_and_month yymmn.;
datalines;
"ID","GENDER","BIRTH YEAR AND MONTH"
"M0058","Female","196106"
"M00123","Male","195108"
"M00331","Male","196002"
"M00366","Female","195001"
"M00440","Female","195607"
"M00458","Female","196501"
"M00584","Female","197902"
"M00611","Male","196601"
"M00673","Female","194306"
;

The YYMMN informat will set the date to the first of the month. A date is a date is a date, specifying a certain day within a certain month within a certain year. ALWAYS.

View solution in original post

8 REPLIES 8
Tom
Super User Tom
Super User

To convert those strings into dates you can use the YYMMN informat.  Once you have date values it does not matter what format you are using to display them.

data want;
  infile example dsd firstobs=2 truncover ;
  input id :$10. gender :$6. birthym :yymmn.;
  format birthym yymmdd10.;
run;
Obs      id      gender       birthym

 1     M0058     Female    1961-06-01
 2     M00123    Male      1951-08-01
 3     M00331    Male      1960-02-01
 4     M00366    Female    1950-01-01
 5     M00440    Female    1956-07-01
 6     M00458    Female    1965-01-01
 7     M00584    Female    1979-02-01
 8     M00611    Male      1966-01-01
 9     M00673    Female    1943-06-01

 

mantubiradar19
Quartz | Level 8
Let me try this tom
ballardw
Super User

DATE implies a day of month as well, which will be important if you want of calculate intervals.

Which day of the month should be used as a default for those "dates"?

mantubiradar19
Quartz | Level 8
I would like to calculate the interval using only the YEAR. Ex: 2020 - 1961 for my first observation
Kurt_Bremser
Super User

@mantubiradar19 wrote:
I would like to calculate the interval using only the YEAR. Ex: 2020 - 1961 for my first observation

If you only need a year difference, the day of the month should be irrelevant.

Use the YYMMN6. informat to read the data, and then the INTCK or YRDIF function to compare two dates; if the second value is year-only, extract the year from the date with the YEAR function.

mantubiradar19
Quartz | Level 8
If I use the following code, the date of "1" gets added to the birthym

data My_data;
infile "My_data.csv" dsd firstobs=2 truncover ;
input ID :$11. GENDER :$8. BIRTH_YEAR_AND_MONTH :yymmn.;
format BIRTH_YEAR_AND_MONTH yymmdd10.;
run;quit;

So, I should just change ":yymmn." to ":YYMMN6." right?
Kurt_Bremser
Super User

Nothing's added:

data My_data;
infile datalines dlm="," dsd firstobs=2 truncover;
input id :$11. gender :$8. birth_year_and_month :yymmn.;
format birth_year_and_month yymmn.;
datalines;
"ID","GENDER","BIRTH YEAR AND MONTH"
"M0058","Female","196106"
"M00123","Male","195108"
"M00331","Male","196002"
"M00366","Female","195001"
"M00440","Female","195607"
"M00458","Female","196501"
"M00584","Female","197902"
"M00611","Male","196601"
"M00673","Female","194306"
;

The YYMMN informat will set the date to the first of the month. A date is a date is a date, specifying a certain day within a certain month within a certain year. ALWAYS.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 693 views
  • 3 likes
  • 4 in conversation