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
;
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?
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.