topic Re: How to group by condition using SQL? in SAS Programming

How to group by condition using SQL?

yanshuai — Thu, 01 Mar 2018 22:13:31 GMT

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!

Re: How to group by condition using SQL?

Reeza — Thu, 01 Mar 2018 22:15:15 GMT

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.

Re: How to group by condition using SQL?

PGStats — Thu, 01 Mar 2018 22:29:14 GMT

If both variables exist but one value is missing, use

group by coalesce(YEAR, FYEAR)

Re: How to group by condition using SQL?

yanshuai — Thu, 01 Mar 2018 23:34:20 GMT

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

Can I still use this code?

Re: How to group by condition using SQL?

PGStats — Fri, 02 Mar 2018 03:29:45 GMT

Yes, precicely.

Re: How to group by condition using SQL?

Reeza — Fri, 02 Mar 2018 03:44:49 GMT

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

Re: How to group by condition using SQL?

Reeza — Fri, 02 Mar 2018 03:46:11 GMT

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

Re: How to group by condition using SQL?

PGStats — Fri, 02 Mar 2018 04:12:14 GMT

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;