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!
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.
If both variables exist but one value is missing, use
group by coalesce(YEAR, FYEAR)
No they are both existing. Just some datasets have YEAR only, some datasets have FYEAR only.
Can I still use this code?
Yes, precicely.
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
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.