topic Re: Convert date to a quarter-Year when some dates are missing in SAS Programming

Convert date to a quarter-Year when some dates are missing

d0816 — Fri, 02 Nov 2018 21:03:17 GMT

I have a date variable in YYMMDD10. format. e.g. 2017-06-26, I want to create another variable with Quarter and Year of the date variable .e.g Q2 2017 (please note the space in between). Some of the values for the date variable is missing and I cant delete rows with missing values.

Following code only works when there is no missing date values and returns quarter as Year-Quarter . eg. 2017Q2

data want; set have;

EndQuarter=put(EndDate, yyq.);

run;

But For missing date values, following code did not work.

data want; set have;

if EndDate=. then EndQuarter=.;

else EndQuarter=put(EndDate, yyq.);

run;

Any suggestion for changes?

Thank you.

Re: Convert date to a quarter-Year when some dates are missing

novinosrin — Fri, 02 Nov 2018 21:10:55 GMT

Your code works fine for me

data w;

enddate= input('2017-06-26',yymmdd10.);
length EndQuarter $10;
if EndDate=. then EndQuarter=' ';

else EndQuarter=put(EndDate, yyq6.);
output;
enddate=.;

if EndDate=. then EndQuarter=' ';

else EndQuarter=put(EndDate, yyq6.);
output;
format enddate yymmdd10.;
run;

Re: Convert date to a quarter-Year when some dates are missing

SASKiwi — Fri, 02 Nov 2018 21:12:48 GMT

Why do you need to define your quarter year date as character? You can just leave it as a SAS date and then apply the date format you want:

EndQuarter=EndDate;
format EndQuarter yyq.;

Re: Convert date to a quarter-Year when some dates are missing

d0816 — Fri, 02 Nov 2018 21:18:54 GMT

I got this error with the suggested code:

The format $YYQ was not found or could not be loaded.

Re: Convert date to a quarter-Year when some dates are missing

novinosrin — Fri, 02 Nov 2018 21:19:57 GMT

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000201247.htm

Re: Convert date to a quarter-Year when some dates are missing

Reeza — Fri, 02 Nov 2018 21:32:21 GMT

The dollar sign means that your date variable is not a SAS date, but it's a character. So you first have to import it as a date using INPUT and then convert it to YYQ using PUT, or apply the format. This is easy to see in the future, because it has the $ sign in front of the format and that's only true for character formats, not numeric formats.

Re: Convert date to a quarter-Year when some dates are missing

ballardw — Fri, 02 Nov 2018 22:14:19 GMT

data want; set have; 

if EndDate=. then EndQuarter=.; <= creates endquarter as numeric

else EndQuarter=put(EndDate, yyq.); <= attempts to put non-numeric value Q into a number.

run;

Try

data want; 
   set have; 
   length Enddate $ 6.;
   if EndDate=. then EndQuarter= ' ';

   else EndQuarter=put(EndDate, yyq.);

run;

Though for most purposes I would just use the format yyq. for Enddate .

Re: Convert date to a quarter-Year when some dates are missing

d0816 — Tue, 20 Nov 2018 22:09:35 GMT

My variable was a string variable , so first I converted to numeric, as Reeza suggested. That did the trick. And then applied the format.