topic Re: how to read change missing date values. in New SAS User

how to read change missing date values.

saidatta — Wed, 19 May 2021 05:44:16 GMT

data ex1;
input start yymmdd10. end yymmdd10.;
format start yymmdd10. end yymmdd10.;
cards;
2020-02-03 2020-04-03
2020-06 2020-06
2020-12 2020-12
2020 2020
run;

i don't know where im doing wrong the missing data have to read and show it as missing but it shows every thing as missing and throws errors only reading first observation please help me

thank you in advance...

Re: how to read change missing date values.

Kurt_Bremser — Wed, 19 May 2021 07:49:06 GMT

Since a date is a specific day, a string without day or month parts cannot be read as a date.

You have to first define rules for creating fictitious month and day values when they are missing.

@saidatta wrote:

data ex1;
input start yymmdd10. end yymmdd10.;
format start yymmdd10. end yymmdd10.;
cards;
2020-02-03 2020-04-03
2020-06 2020-06
2020-12 2020-12
2020 2020
run;

i don't know where im doing wrong the missing data have to read and show it as missing but it shows every thing as missing and throws errors only reading first observation please help me

thank you in advance...

Re: how to read change missing date values.

Ksharp — Wed, 19 May 2021 12:18:54 GMT

Please post the output you want to see .

Re: how to read change missing date values.

AMSAS — Wed, 19 May 2021 13:50:44 GMT

@saidatta As @Kurt_Bremser stated you are going to need to define some rules to handle dates without month and/or day.
Still here's a way to solve the issue, note I used a simple rule that states if day or month are missing then assume them to be 01 (i.e. 2020 would become 01Jan2020, 2020-03 becomes 01Mar2020).

The code uses Pattern Matching Using Perl Regular Expressions (PRX) to identify the format of the input, then takes appropriate action to convert that into a SAS date value

data ex1;
	retain regExp 0 ;
	if _n_=1 then do ;	
		/* Create a regular expression to identify the different date formats */
		regExp=prxparse("/(\d{4}-\d{2}-\d{2})|(\d{4}-\d{2})|(\d{4})/") ;
	end ;
	input cStart : $10. cEnd : $10. ;
	format start date7. ;
	/* Does cStart contain a format we are looking for? */
	match=prxmatch(regExp,cStart) ;
	if match then do ;
		/* It does! So which format did it have? */
		paren=prxparen(regExp) ;
		put cStart= match= paren= ;
		/* Format 1 is YYYY-MM-DD */
		if paren=1 then do ;
			/* don't do anything here, but we could */			
		end ;
		/* Format 2 is YYYY-MM */
		/* So we will need a rule to set the DD, I used 01" */
		else if paren=2 then do ;
			cStart=trim(cStart)!!"-01" ;
		end ;
		/* Format 3 is YYYY */
		/* So we will need a rule to set the MM and DD, I used -01-01" */
		else if paren=3 then do ;
			cStart=trim(cStart)!!"-01-01" ;
		end ;
		/* Now convert cStart to a SAS date value */ 
		start=inputn(cStart,"yymmdd10.") ;
		put "FINALLY! " cStart= start=date7. ;

	end ;

	
cards;
2020-02-03 2020-04-03
2020-06 2020-06
2020-12 2020-12
2020 2020
run;

Re: how to read change missing date values.

Kurt_Bremser — Wed, 19 May 2021 14:28:27 GMT

Since (as you already experienced) you can't read the data with the default YYMMDD informat, you need to first read the string into a character variable, and then convert that, while making assumptions for the missing values.

For this, since the code needs to be reusable, I created this macro:

%macro get_date(in,out);
&out. = mdy(
  coalesce(input(scan(&in.,2,"-"),2.),1),
  coalesce(input(scan(&in.,3,"-"),2.),1),
  input(scan(&in.,1,"-"),4.)
);
drop &in.;
format &out. yymmdd10.;
%mend;

The string is dissected into three parts, using the SCAN function, and those parts are individually converted to numbers; if a part is missing, the INPUT function will happily return a missing value from the empty string without further complaint.

By applying the COALESCE function, we then set default values if a certain part is missing, and finally, the MDY function builds the complete date.

We also assign a format and drop the "in" variable, as it is no longer needed.

The code to read the data then will look like this:

data want;
input c_start :$10. c_end :$10.;
%get_date(c_start,start);
%get_date(c_end,end);
datalines;
2020-02-03 2020-04-03
2020-06 2020-06
2020-12 2020-12
2020 2020
;

proc print data=want noobs;
run;

resulting in

start	end
2020-02-03	2020-04-03
2020-06-01	2020-06-01
2020-12-01	2020-12-01
2020-01-01	2020-01-01

Re: how to read change missing date values.

ballardw — Wed, 19 May 2021 15:25:27 GMT

A different way to parse the date lengths and read to a standard date

data ex1;
   input startchar :$10. endchar :$10.;
   array s (*) startchar endchar;
   array d (*) start end;
   do i= 1 to dim(s);
      select (length(s[i]) );
         when(10) d[i]= input(s[i],yymmdd10.);
         when(7) d[i]= input(catx('-',s[i],'01'),yymmdd10.);
         when(4) d[i]= input(catx('-',s[i],'01','01'),yymmdd10.);
         when(0) d[i]= .;
         otherwise Put "Expected length for: " s[i]=;
      end;
   end;
   drop i;
   format start yymmdd10. end yymmdd10.;
cards;
2020-02-03 2020-04-03
2020-06 2020-06
2020-12 2020-12
2020 2020
;

If you get any "unexpected lengths" then some additional code will be needed to address the specific cases.

This sets missing month and year to 01 Jan for a year, or missing day of month to day=0.

Re: how to read change missing date values.

Kurt_Bremser — Wed, 19 May 2021 14:34:46 GMT

PS don't do this:

cards;
2020-02-03 2020-04-03
2020-06 2020-06
2020-12 2020-12
2020 2020
run;

The CARDS or DATALINES block should be terminated by a single semicolon in column 1 on its own line.

By using what is seemingly a RUN statement (but it isn't, see the coloring done by the Enhanced Editor), you might ending up receiving the string "run" as data!

Since the CARDS or DATALINES block, by definition, is the last part of a DATA step, it also constitutes a step boundary, so no RUN is needed at all.

But if you absolutely like to have a RUN there, do it like this:

cards;
2020-02-03 2020-04-03
2020-06 2020-06
2020-12 2020-12
2020 2020
;
run;

Re: how to read change missing date values.

saidatta — Wed, 19 May 2021 14:48:20 GMT

and also for end variable the date that i want to in end date of the month basing on feb and leap etc.

thanks in advance and sorry for my English i'm learning..

Re: how to read change missing date values.

saidatta — Wed, 19 May 2021 14:52:00 GMT

thank you so much that helped me a lot and mainly that line to line comment simply super and awesome
thanks again for your hard work

Re: how to read change missing date values.

Ksharp — Thu, 20 May 2021 12:00:39 GMT

data want;
input c_start :$10. c_end :$10.;

start='1900-01-01';
end='1900-01-01';

substr(start,1,lengthn(c_start))=substr(c_start,1,lengthn(c_start));
substr(end,1,lengthn(c_end))=substr(c_end,1,lengthn(c_end));

num_start=input(start,yymmdd12.);
num_end=input(end,yymmdd12.);

format num_start num_end yymmdd10.;

datalines;
2020-02-03 2020-04-03
2020-06 2020-06
2020-12 2020-12
2020 2020
;