topic How to populate a variable based on what part of date is missing form a date variable in SAS Programming

How to populate a variable based on what part of date is missing form a date variable

knveraraju91 — Mon, 16 Nov 2020 03:31:55 GMT

Dear,

I need to prepare a code for the below situation. There is a character variable with yymmdd10. format. For some OBS, either year or month or date or all are missing. I need to create a variable for what part is missing. Please suggest in data step two below code. Thank you

if date is complete or missing dtf should be blank (for id= a and f)

if day is missing then dtf=d (id= b)

if day and month is missing then dtf=m (id= c)

if year is missing then dtf=y (id= d)

if month is missing and day is present then dtf=m (id= e)

data one;
input id$ date $10.;
datalines;
a 2017-07-25
b 2017-07
c 2017
d -07-25
e 2017--25
f
;

data two;
set one;
want=choosec(countw(date, "-"),"M", "D", " ");
run;

output needed

id date dtf

a 2017-07-25
b 2017-07 D
c 2017 M
d -07-25 Y
e 2017--25 M
f
;

Re: How to populate a variable based on what part of date is missing form a date variable

SASKiwi — Mon, 16 Nov 2020 03:52:06 GMT

What are your rules for populating the missing data?

For example if day is missing, but month and year are what day do you want - day 1 or something else?

Re: How to populate a variable based on what part of date is missing form a date variable

Tom — Mon, 16 Nov 2020 04:03:06 GMT

Why not just use the logic in the question in the code instead of trying to generate some type of one liner?

I added an additional category to handle the case when none of the values are missing but they do not represent a valid date.

data test;
  input id $ date $10.;
  if missing(date) or not missing(input(date,??yymmdd10.)) then dtf=' ';
  else if missing(scan(date,1,'-','m')) then dtf='Y';
  else if missing(scan(date,2,'-','m')) then dtf='M';
  else if missing(scan(date,3,'-','m')) then dtf='D';
  else dtf='X';
datalines;
a 2017-07-25
b 2017-07
c 2017
d -07-25
e 2017--25
f
g 2017-02-31
;

Results

Obs    id    date          dtf

 1     a     2017-07-25
 2     b     2017-07        D
 3     c     2017           M
 4     d     -07-25         Y
 5     e     2017--25       M
 6     f
 7     g     2017-02-31     X

Re: How to populate a variable based on what part of date is missing form a date variable

Kurt_Bremser — Mon, 16 Nov 2020 08:10:02 GMT

See this:

data want;
set one;
year = input(scan(date,1,"-","m"),4.);
month = input(scan(date,2,"-","m"),2.);
day = input(scan(date,3,"-","m"),2.);
if year and month and day or not year and not month and not day
then dtf = " ";
else if not year
then dtf = "y";
else if not month
then dtf = "m";
else dtf = "d";
run;

With incomplete dates, I would keep separate year/month/day variables, to enable end users to deal with the missing values in a way that is appropriate for their current needs.