DATA Step, Macro, Functions and more

How to group by condition using SQL?

Reply
Contributor
Posts: 50

How to group by condition using SQL?

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!

Super User
Posts: 24,028

Re: How to group by condition using SQL?

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.

Esteemed Advisor
Posts: 5,628

Re: How to group by condition using SQL?

If both variables exist but one value is missing, use

 

group by coalesce(YEAR, FYEAR)

PG
Contributor
Posts: 50

Re: How to group by condition using SQL?

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

Can I still use this code?

Esteemed Advisor
Posts: 5,628

Re: How to group by condition using SQL?

Yes, precicely.

PG
Super User
Posts: 24,028

Re: How to group by condition using SQL?

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
Super User
Posts: 24,028

Re: How to group by condition using SQL?


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. 

Esteemed Advisor
Posts: 5,628

Re: How to group by condition using SQL?

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;
PG
Ask a Question
Discussion stats
  • 7 replies
  • 126 views
  • 2 likes
  • 3 in conversation