How to group by condition using SQL?

[yanshuai]

I am trying to capture a certain variable according to certain variable.

data have ;
input
YEAR SALE ID $5.;
datalines;
2001  12  ab34
2001  10  1234
2001  14  ab34
2001  10  2234
2002  10  1234
2002  10  ab34
2002  10  2234;
run;

Some dataset has variable YEAR, but some has FYEAR, how can I group by YEAR, and group by FYEAR when YEAR is missing?

Can they be written in the GROUP BY code?

proc sql;
create table want as
SUM(SALE) as TOTALSALE
from have
group by YEAR or FYEAR; /*How can I realize this step using code? SQL did not recgonize OR*/
quit;

Thank you very much!

[Reeza]

If the variable doesn't exist that's problematic. 

I would suggest using a macro to check if it exists and if it does uses the correct variable. Or rename them all to be standardized.

[PGStats]

If both variables exist but one value is missing, use

group by coalesce(YEAR, FYEAR)

[yanshuai]

No they are both existing. Just some datasets have YEAR only, some datasets have FYEAR only.

Can I still use this code?

[PGStats]

Yes, precicely.

[Reeza]

No, if the variable doesn't exist it won't work. 

 69         proc sql;
 70         create table want as
 71         select *
 72         from sashelp.class
 73         order by coalesce(age, new_age);
 ERROR: The COALESCE function requires its arguments to be of the same data type.
 ERROR: The following columns were not found in the contributing tables: new_age.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 74         quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

[Reeza]

---

@yanshuai wrote:

No they are both existing. Just some datasets have YEAR only, some datasets have FYEAR only.

Can I still use this code?

---

@PGStats is asking if they both exist in the same data set, you answer seems to indicate that it doesn't, therefore coalesce will not work. 

[PGStats]

You can force both variables to exist with an extra step such as:

data have ;
input
YEAR SALE ID $5.;
datalines;
2001  12  ab34
2001  10  1234
2001  14  ab34
2001  10  2234
2002  10  1234
2002  10  ab34
2002  10  2234
;

data both;
length year fyear 8;
set have;
run;

proc sql;
create table want as
select
coalesce(YEAR, FYEAR) as year,
SUM(SALE) as TOTALSALE
from both
group by calculated year;
quit;