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

[d0816]

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.

[novinosrin]

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;

[SASKiwi]

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.;

[d0816]

I got this error with the suggested code:

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

[novinosrin]

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

[Reeza · Accepted solution]

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.

[d0816]

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

[ballardw]

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 .